Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2011
    Posts
    13

    Unanswered: alais name in the group by clause

    Hello,
    I have a situation where I have to use alais name for a field in the group by caluse. But Db2 is not allowing that. Can you please suggest this can be attained.
    Below is the situation:
    I have 2 tables tab1 and tab2. tab1 has fields A, B, C, D and tab2 has fields E, F, G,H. My query goes like this

    SELECT count(*), COALESCE(tab2.G,tab1.C) as TID
    FROM tab1
    LEFT JOIN tab2 ON
    tab2.E=tab1.A and tab2.F=tab1.B
    WHERE tab1.d > 20
    GROUP BY TID

    But this query gives me error saying TID

    Thanks,
    Thamizh

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by thamin View Post
    I have to use alais name for a field in the group by caluse.
    Why do you have to use it? Just use "GROUP BY COALESCE(tab2.G,tab1.C)"/

  3. #3
    Join Date
    Jul 2011
    Posts
    13
    Thank you very much......

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please see the topic...
    subselect - IBM DB2 9.7 for Linux, UNIX, and Windows

    You will see...
    The clauses of the subselect are processed in the following sequence:
    1.FROM clause
    2.WHERE clause
    3.GROUP BY clause
    4.HAVING clause
    5.SELECT clause
    6.ORDER BY clause
    7.FETCH FIRST clause
    GROUP BY clause is processed before SELECT clause.
    So, "AS new-column-name" in SELECT clause are not known in GROUP BY clause.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another way may be use of nested-table-expression, like ...
    Code:
    SELECT count(*) , tid
     FROM  (SELECT COALESCE(tab2.G , tab1.C) AS tid
             FROM  tab1
             LEFT  JOIN
                   tab2
              ON   tab2.E = tab1.A
               AND tab2.F = tab1.B
             WHERE tab1.d > 20
           ) s
     GROUP BY tid
    ;

Posting Permissions

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