Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Posts
    9

    Unanswered: error while trying to query a union of SQLs

    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
    Last edited by krajkumar6; 10-13-03 at 03:17.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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
    ....

  3. #3
    Join Date
    Sep 2003
    Posts
    9
    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 ' .

  4. #4
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    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............
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •