Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2003
    Posts
    32

    Unanswered: A query Join the union result

    SELECT a, M.c
    FROM
    (SELECT d AS a
    FROM X
    UNION ALL
    SELECT e AS a
    FROM Y) Z, M
    WHERE Z.a = M.b

    Give me error “Column c or expression in SELECT list not valid.”

    What is the problem?
    Thanks,

    Lida

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Use a CTE:

    with t1 (a) as (SELECT d AS a
    FROM X
    UNION ALL
    SELECT e AS a
    FROM Y) select t.a,m.c from t1 as t inner join m as m on (t.a = m.b)

    Andy

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What platform and DB2 version/release are you using?

    Your query worked on my DB2 V9.5 on Windows XP.
    Code:
    ------------------------------ Commands Entered ------------------------------
    connect to SAMPLE ;
    ------------------------------------------------------------------------------
    
       Database Connection Information
    
     Database server        = DB2/NT 9.5.2
     SQL authorization ID   = DB2ADMIN
     Local database alias   = SAMPLE
    
    
    A JDBC connection to the target has succeeded.
    ------------------------------ Commands Entered ------------------------------
    WITH
     X(d) AS (VALUES
    1, 2, 5
    )
    ,Y(e) AS (VALUES
    1, 3, 4, 5
    )
    ,M(b, c) AS (VALUES
     (1, 'aaa')
    ,(2, 'bbb')
    ,(3, 'ccc')
    )
    SELECT a, M.c
    FROM 
    (SELECT d AS a
    FROM X
    UNION ALL
    SELECT e AS a
    FROM Y) Z, M
    WHERE Z.a = M.b
    ;
    ------------------------------------------------------------------------------
    
    A           C  
    ----------- ---
              1 aaa
              3 ccc
              1 aaa
              2 bbb
    
      4 record(s) selected.

  4. #4
    Join Date
    Sep 2003
    Posts
    32

    Join Union result

    Hi Andy,

    I got the same error: "Column c or expression in SELECT list not valid"
    Code:
    with t1 (a) as (SELECT d AS a
    FROM X
    UNION ALL
    SELECT e AS a
    FROM Y) select t.a,m.c from t1 as t inner join m as m on (t.a = m.b)
    My version is AS400 v5r4, I use ODBC to query.

    Thanks,

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Then I would suspect that table M has no column C.

    Andy

  6. #6
    Join Date
    Sep 2003
    Posts
    32
    I even tried: (select m.b, which is the join field)
    with t1 (a) as (SELECT d AS a
    FROM X
    UNION ALL
    SELECT e AS a
    FROM Y) select t.a,m.b from t1 as t inner join m as m on (t.a = m.b)

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by newer
    I even tried: (select m.b, which is the join field)
    with t1 (a) as (SELECT d AS a
    FROM X
    UNION ALL
    SELECT e AS a
    FROM Y) select t.a,m.b from t1 as t inner join m as m on (t.a = m.b)
    And what does this give you?

    Andy

  8. #8
    Join Date
    Sep 2003
    Posts
    32
    Andy,
    The same error. I am sure there is no problem with field (or column) name. There is no problem if I join first then Union, but the performance will be better if Union first then Join since I will only need top 5. So don't think there is any problem with column name. Actually, the query you post has no difference with my original query.

  9. #9
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    How can it give you the same error if you do not reference column C? Can you post the entire error message complete with SQLCODE and SQLSTATE?

    Andy

  10. #10
    Join Date
    Sep 2003
    Posts
    32
    ok, The error message complaint about column m.b if I select column m.b.

    I almost don't think there is any problem with Query. It is because of version. tonkuma even tested my query with no problem. Thank you any way.

Posting Permissions

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