[ic] complex sql select statements

[interchange-users@icdevgroup.org (Mike Heins)]

Quoting John Allman (allmanj@houseofireland.com):
> 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.

I can almost guarantee you need a base=cattable in your [query ...]
tag. 

-- 
Mike Heins
Perusion -- Expert Interchange Consulting    http://www.perusion.com/
phone +1.513.523.7621      <mike@perusion.com>

Be patient. God isn't finished with me yet.  -- unknown