[ic] complex sql select statements

[interchange-users@icdevgroup.org (John Allman)]

Mike Heins wrote:

>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. 
>
i added in base=cattable in the query tag and i still get errors 
generated. my post in the nuttyness thread also has base=cattable and it 
has made no difference.

can you think of anything else it might be?

thanks

John

>  
>