Hi,

I am working on DB2 on AS 400
V5R1M0 010525 .
I have a SQL which looks like this.
In this query the union portion alone works fine.


select * from
(
select
T1.C1,T2.C2
from
T1,T2
where
....
group by
...

UNION ALL

select T3.C1,T2.C2

from
T3,T2
where
....
GROUP BY
....
) test

But when I try to select * from the union I get the
error

SQL0199] Keyword UNION not expected. Valid tokens: ). Cause . . . . . : The keyword UNION was not expected here. A syntax error was detected at keyword UNION. The partial list of valid tokens is ). This list assumes that the statement is correct up to the unexpected keyword. The error may be earlier in the statement but the syntax of the statement seems to be valid up to this point. Recovery . . . : Examine the SQL statement in the area of the specified keyword. A colon or SQL delimiter may be missing. SQL requires reserved words to be delimited when they are used as a name. Correct the SQL statement and try the request again.

Can someone tell me how this error can be overcome?

K Rajkumar

Why do you need the select *?

All you need is:

select
T1.C1,T2.C2
from
T1,T2
where
....
group by
...

UNION ALL

select T3.C1,T2.C2
from
T3,T2
where
....
GROUP BY
....

I have a requirement to use group by on the
set of rows that are returned by the union of SQLs.

I used 'Select * from ' just to explain the problem to you.
My actual requirement has a select statement with a group by
in it ,in place of 'Select * from ' .

The select over a temporary set as you are using works correctly in DB2 UDB, the same syntax in DB2 / AS400 does not work, no matter what I try with variations on the syntax. I wonder whether this is at all possible with DB2/AS400............