OCIStmtExecute: ORA-00936

[Anon]

I am getting the following error:

Warning: OCIStmtExecute: ORA-00936: missing expression in /u04/web/shopatron/pages/Dev/index.phtml on line 61
That I am not able to understand why. I am believe that I am passing it all of the correct parameters but still I get an error. ANy help would be greatly appreciated.

line 61 is:
$exec = OCIExecute($sql_st);

from the following code block:

$sql = "select distinct category from product p, mfg_relations mr
where p.mfg_id = mr.mfg_id1
and p.deleted = 'N'
and mr.mfg_id = $mfg_id
and mr.restriction = 'only'
and ((mr.affected_level = 'category' or p.category = mr.affected_unit)
or(mr.affected_level = 'all')
or(mr.affected_level = 'subcategory' and p.subcategory = mr.affected_unit)
or(mr.affected_level = 'item' and p.product_id = mr.affected_unit)
)
and (Select distinct category from product
where mfg_id = $mfg_id
and deleted = 'N')
order by category";

$sql_st = OCIParse($conn,$sql);
$exec = OCIExecute($sql_st);
$col = 0;
while (OCIFetchInto($sql_st, $row, OCI_ASSOC)) {
$category=$row[CATEGORY];

if ($col == 0) { echo "<tr>";} else {echo "<td width = 20>&nbsp;</td>";}

echo "<td>$font<A HREF='$server_url/sub_category.phtml?category=".urlencode($category)."'>$row[CATEGORY]</A></font></td>";

if ($col == 1) {
echo "</tr>";
$col = 0;
} else {
$col = 1;
}
}

[kenpty]

Hi there,

Instead of:
$sql = "select distinct category from product p, mfg_relations mr

It should be:
$sql = "select distinct category from product p, mfg_relations , mr

you have miss a ","

[Anon]

THats funny I thought the proper way to build a shorthand reference for a table is to do <tableName> <abbreviation, <tableName2> <abbreviation2>

so mr was a shorthand of mfg_relations...

if that is the case the comma wouldnt do the trick....

[Anon]

I am still getting a missing expression error for the follwoing line :

$exec = OCIExecute($sql_st); in the code block pasted below. But I have modified the sql statement so that it actually works. I found out that an OCIParse command will return false if the SQL statement it is trying errors out. SO I validated the SQL statement in SQLPlus then stuck it back into the code and tried to echo the result of the OCIParse statement how come it doesnt result with the SQL statement?

My error has got to be in either the OICParse statement or I guess the OICExecute.... your help greatly appreciated.

Here is the updated code block:
$sql = "select distinct category from product p, mfg_relations mr
where p.mfg_id = mr.mfg_id1
and p.deleted = 'N'
and mr.mfg_id = $mfg_id
and mr.restriction = 'only'
and ((mr.affected_level = 'category' or p.category = mr.affected_unit)
or(mr.affected_level = 'all')
or(mr.affected_level = 'subcategory' and p.subcategory = mr.affected_unit)
or(mr.affected_level = 'item' and p.product_id = mr.affected_unit))
UNION
Select distinct category from product
where mfg_id = $mfg_id
and deleted = 'N';";

$sql_st = OCIParse($conn,$sql);
echo "the sql_st = ".$sql_st."<BR>";
$exec = OCIExecute($sql_st);

$col = 0;
while (OCIFetchInto($sql_st, $row, OCI_ASSOC)) {
$category=$row[CATEGORY];

if ($col == 0) { echo "<tr>";} else {echo "<td width = 20>&nbsp;</td>";}

echo "<td>$font<A HREF='$server_url/sub_category.phtml?category=".urlencode($category)."'>$row[CATEGORY]</A></font></td>";

if ($col == 1) {
echo "</tr>";
$col = 0;
} else {
$col = 1;
}
}