Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Posts
    3

    Unanswered: Using UNION in WITH statement [SQL0199]

    DB2 for OS/400 V5R1
    Using the "Run SQL Scripts" program in Ops Nav and also from within a Java program running on the AS/400 using the Java Toolbox (whichever version came with V5R1)

    I am trying to do the following (simplified for clarity):

    WITH temp(a) AS
    (SELECT x
    FROM table1
    UNION
    SELECT x
    FROM table2)
    SELECT a
    FROM temp

    and I get the following error: "[SQL0199] Keyword UNION not expected. Valid tokens: )"

    The tables are union compatible.

    If I try simply doing this:

    WITH temp(a) AS
    (SELECT x
    FROM table1)
    SELECT a
    FROM temp

    it works so I know that the WITH statement is OK.

    Also, if I try it this way:

    SELECT sq.a
    FROM(
    SELECT a
    FROM table1
    UNION
    SELECT a
    FROM table2
    ) AS sq

    I get the same error message.

    I have successfully written many queries like this from within MS Access using Client Access ODBC, but this is the first time I've tried to do it via the JDBC interface. Is this a limitation in DB2, or am I doing something wrong or missing a trick to get it to work?

    Thanks!

  2. #2
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    You probably must write the aliases.
    Try this:

    SELECT sq.a
    FROM(
    SELECT a as sq.a
    FROM table1
    UNION
    SELECT a as sq.a
    FROM table2
    ) AS sq


    Or try this:

    SELECT a
    FROM(
    SELECT a
    FROM table1
    UNION
    SELECT a
    FROM table2
    ) AS sq

    Hope this helps,
    Grofaty


  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: Using UNION in WITH statement [SQL0199]

    Originally posted by carter
    DB2 for OS/400 V5R1
    WITH temp(a) AS
    (SELECT x
    FROM table1
    UNION
    SELECT x
    FROM table2)
    SELECT a
    FROM temp

    and I get the following error: "[SQL0199] Keyword UNION not expected. Valid tokens: )"
    I think you cannot use this syntax (full-selects in WITH and FROM clauses) on iSeries. Check the manual:

    http://publib.boulder.ibm.com/iserie...2/rbafzmst.pdf
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Sep 2003
    Posts
    3
    I did some more searching and found this:

    IBM Software Technical Document
    __________________________________________________ ________________
    Document Information
    __________________________________________________ ________________

    Document Title
    FULLSELECT on Derived Tables Support

    Document Description
    In R520, a FULLSELECT on derived tables can now be done. In previous releases, trying this may have resulted in the following message: SQL0199 - Message . . . . : Keyword UNION not expected. Valid tokens: ).

    The FULLSELECT is a component of the select-statement and the CREATE VIEW statement. A FULLSELECT enclosed in parenthesis is called a subquery. A FULLSELECT specifies a result table. If UNION is not used, the result of the FULLSELECT is the result of the specified subselect. UNION derives a result table by combining two other result tables. The following is a simple example of how support for FULLSELECT of a derived table can work at R520:

    select sum(derived.fld1) from (select fld1 from filea UNION select fld1 from fileb) as Derived
    This is documented in the SQL Reference manual in the chapter called Queries found in the iSeries Information Center

    --------------------------------------------------------

    Time to upgrade to V5R2! In the mean time, I'll just build a temporary intermediate table.

    Thanks for your help.
    Mike
    Last edited by carter; 09-04-03 at 12:16.

  5. #5
    Join Date
    Jan 2002
    Location
    USA
    Posts
    53
    try following:

    WITH temp(a) AS
    (SELECT x
    FROM table1
    UNION all
    SELECT x
    FROM table2)
    SELECT a
    FROM temp


    WTH


    ljrong

  6. #6
    Join Date
    Sep 2003
    Posts
    3
    Originally posted by ljrong
    try following:

    WITH temp(a) AS
    (SELECT x
    FROM table1
    UNION all
    SELECT x
    FROM table2)
    SELECT a
    FROM temp


    WTH


    ljrong
    Nope... it still chokes.

Posting Permissions

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