[ic] sql query with UNION broken

Mike Heins mike at perusion.com
Fri Aug 22 11:33:57 EDT 2003


Quoting Bill Carr (bill at worldwideimpact.com):
> IC 4.9.8, MySQL 4.0.14, PERL 5.8
> 
> I am trying to do a SQL query using UNION. When I add parentheses around
> each of my SELECT statements I do not get the full results. I only get
> the values from the first column of the first row. I need the
> parentheses because I would like to sort the results of the UNION.
> 
> This gives the desired results:
> [query
>         list=1
>         sql=|
>                 SELECT 1,2
>                 UNION
>                 SELECT 3,4
>                 |
> ]
>         [list]
>                 [sql-pos 0], [sql-pos 1]<br>
>         [/list]
> [/query]
> Yields:
> 	1,2
> 	3,4
> 
> Adding parentheses breaks it:
> [query
>         list=1
>         sql=|
>                 (SELECT 1,2)
>                 UNION
>                 (SELECT 3,4)
>                 |
> ]
>         [list]
>                 [sql-pos 0], [sql-pos 1]<br>
>         [/list]
> [/query]
> Yields:
> 	2,
> 
> 
> Is this bug? Any ideas?

Make sure you do

	[query table=tablename sql=...]

If the table is not in the same database as products Vend::SQL_Parser
might get a hold of it and I don't think it handles UNION (at least yet).

Otherwise, it may be that DBI has a problem with it. I would do:

<xmp>
[query list=1 sql="(your query)"][sql-line]
[/query]
</xmp>

just to see what you are getting back from the query. At that point,
you might be able to figure out what is going on.

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

I have a cop friend who thinks he ought be able to give a new ticket;
"too dumb for conditions".


More information about the interchange-users mailing list