[ic] complex sql select statements

John Allman interchange-users@icdevgroup.org
Tue Sep 10 12:24:01 2002


Mike Heins wrote:

>Quoting John Allman (allmanj@houseofireland.com):
> >>
>  
>
>>>>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?
>>    
>>
>
>Maybe "table=cattable"? I thought there was an attrAlias there, but maybe
>not.
>
>Only that it is really an invalid query, and the reason you get the error pointing
>to Scan.pm is that it fails in $db->query(). I use this same methodology
>in several places, and if your query/check_orders page works you know it
>is OK in your install.
>
>  
>
i tried table=cattable but that didn't help either:( i'm afraid i dont 
understand your last paragraph. are you saying it is an invalid query? 
the thing that is getting me is that it works in the other catalog.

if it is any help to understanding what is going on - i am running 
interchange 4.6.5. the catalog that generates the errors with that page 
is connecting to two mysql databases: one for cattable and products and 
another for the rest. the other catalog has only one database connection 
to the same database with cattable and products in it. as i say, the 
page generates no errors in the catalog with one database, but does in 
the catalog with two. i can now also confirm that the page in the 
nuttyness thread also works fine in the catalog with one database but 
not in the one with two.

any ideas?