Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2004
    Posts
    19

    Unanswered: Union inside select

    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.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Try it like this:


    WITH temp1 as
    (

    SELECT ....

    UNION ALL

    SELECT ....

    )
    SELECT

    something, something_else from temp1

    Andy

  3. #3
    Join Date
    Aug 2004
    Posts
    19
    I am getting a syntax error on that

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Please post your entire query.
    Andy

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    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.

Posting Permissions

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