UNION queries in Query Templates

[Former Member]

When I code a "UNION ALL" clause in a query template, XMII seems to ignore the second portion of the SQL. That is, if I do:

SELECT COLA, COLB FROM ....

UNION ALL

SELECT COLB, COLB FROM ...

the results for the first portion of the clause are returned but not the second portion. The SQL works fine in TOAD, just not in XMII whether as a result of testing the query in the editor or calling the query in a Trx.

Has anybody else struck this problem?

Cheers.

[jcgood25 · Accepted solution]

Laurie,

Is there any chance that you are not getting the results from the second query because the first SELECT statement returns more than the query template's RowCount parameter?

If you need the values sorted you can also add an ORDER BY clause to the second SELECT statement and it should cover the full unioned resultset.

Regards,

Jeremy Good

[Former Member · Accepted solution]

Yeah, thought of that, thanks. It's set to 0 for unlimited.

The sort clause is a good idea, I will see what that does. I will also try it against an SQL Server db to see if maybe it's a problem with the Oracle driver.

Cheers.

Message was edited by:

Laurie Paulin

[jcgood25 · Accepted solution]

RowCount cannot be 0 - it is a safety mechanism to prevent unbound requests.

Since you are working with Oracle make sure that you do the begin/end wrappers for multiple lines (not sure if you need one after the union or not):

BEGIN

SELECT A,B....;

UNION ALL

SELECT A,B....;

END;

Regards,

Jeremy

[Former Member · Accepted solution]

Yep, that was the problem. Setting row count to 0 (as one would do in SQL Server for unlimited rows) means it just defaults back to 100.

Cheers.