[ic] complex sql select statements

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

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

I am saying that the query is failing, so it tries to run the query
through SQL::Statement to determine the table name and see if *that*
table can pass the query. With the complex query, SQL::Statement cannot
determine which table it is.

> the thing that is getting me is that it works in the other catalog.
> 

Then your catalog configurations are different.

If you are stuck on this, you may need a competent consultant to help
you nail it.

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

Nature, to be commanded, must be obeyed. -- Francis Bacon