[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