Union inside select

atmosphere · #1 (**permalink**) 09-01-04, 11:53

Hi

Im trying to select from a table that uses a union with little sucess. i get an sql syntax error.

SELECT

something, something_else

FROM
(

SELECT ....

UNION ALL

SELECT ....

)

NOTE: Everything inside the FROM clause works fine on its own.

ARWinner · #2 (**permalink**) 09-01-04, 12:28

Try it like this:

WITH temp1 as
(

SELECT ....

UNION ALL

SELECT ....

)
SELECT

something, something_else from temp1

Andy

atmosphere · #3 (**permalink**) 09-01-04, 12:54

I am getting a syntax error on that

ARWinner · #4 (**permalink**) 09-01-04, 13:24

Please post your entire query.
Andy

sathyaram_s · #5 (**permalink**) 09-01-04, 18:49

and your platform and db2 version...

CTE( WITH Construct) is not supported on mainframe till v7

Cheers
sathyaram

Quote:

Originally Posted by ARWinner

Please post your entire query.
Andy

Damian Ibbotson · #6 (**permalink**) 09-02-04, 04:11

It's getting confused and thinks you're attempting to write a recursive statment.

Try using the TABLE keyword...

Code:

select cols
from table (
            select cols
            from table1
             union all
            select cols
            from table2
           ) tmpTable
;

Also, think about using UNION ALL in preference to UNION unless there is a good reason to distinct your select.

Internet Services

Web Development

Digital Marketing

Consumer Tech