[ic] complex sql select statements

John Allman interchange-users@icdevgroup.org
Tue Sep 10 06:05:04 2002


hi - i could be way off here but it seems that if i try any sql 
statements with complex clauses i find myself running into problems. i'm 
wondering if there's some sort of flag i need to set in the catalog 
configuration or something. i have taken ic code which works fine on 
another catalog (not created by me and the original author is no longer 
around) and have attempted to move it straight into a new catalog 
without modification since i am using the same database for the relevant 
tables. i was planning on then modifying this code to my needs but to my 
surprise it was not displaying anything like the same thing. i cant 
actually spot anything in catalog.cfg which could be causing the 
difference though...

i cut down the code to just one sql query and found that it was 
generating "Bad SQL" errors in my log file. the same file moved to the 
other catalog generates no errors.

this is the page i am testing in its entirety:

[seti mysqlvar]SELECT 
code,descrip,longdescrip,featprod,template,sku,image,description,price,pricel1,pricel2,pricel3,priceeu,priceeul1,priceeul2,priceiep,priceiepl1,priceiepl2,pricecorp,pricecorpl1,pricecorpl2,pricecompusd 
FROM cattable left outer join products on cattable.featprod=products.sku 
where pcode=252 order by cattable.displorder[/seti]
[query arrayref=myref sql="[scratch mysqlvar]"]
<!-- this container text is not used -->
[/query]

[perl]
        my $ary=$Tmp->{myref};
        $Scratch->{found}="nothing";
        foreach $line(@$ary)
        {
                $Scratch->{found}="something!";
        }
[/perl]
[scratch found]<br>

these are the errors from my log:

192.168.0.145 G6A6upoU:192.168.0.145 - [10/September/2002:10:41:22 
+0100] wat /cgi-bin/wat/subcatcompl.html Bad SQL statement: Parse error 
near left outer join products on cattable.featprod=products.sku where 
pcode=252 order by cattable.displorder at 
//usr/lib/interchange/lib/Vend/Scan.pm line 580.
 >
 > Query was: SELECT 
code,descrip,longdescrip,featprod,template,sku,image,description,price,pricel1,pricel2,pricel3,priceeu,priceeul1,priceeul2,priceiep,priceiepl1,priceiepl2,pricecorp,pricecorpl1,pricecorpl2,pricecompusd 
FROM cattable left outer join products on cattable.featprod=products.sku 
where pcode=252 order by cattable.displorder.
 >
192.168.0.145 G6A6upoU:192.168.0.145 - [10/September/2002:10:41:22 
+0100] wat /cgi-bin/wat/subcatcompl.html Bad SQL, query was: SELECT 
code,descrip,longdescrip,featprod,template,sku,image,description,price,pricel1,pricel2,pricel3,priceeu,priceeul1,priceeul2,priceiep,priceiepl1,priceiepl2,pricecorp,pricecorpl1,pricecorpl2,pricecompusd 
FROM cattable left outer join products on cattable.featprod=products.sku 
where pcode=252 order by cattable.displorder

i have recently posted an update to the thread "nuttyness with database 
select" which describes some odd behaviour with a simple "or" clause in 
a select statement. i suspect the two problems are related...

please please please help. this is beyond my feeble abilities.

thanks,

John