alais name in the group by clause

thamin · #1 (**permalink**) 09-09-11, 10:25

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

n_i · #2 (**permalink**) 09-09-11, 10:39

Quote:

Originally Posted by thamin

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)"/

thamin · #3 (**permalink**) 09-09-11, 10:57

Thank you very much......

tonkuma · #4 (**permalink**) 09-09-11, 10:58

Please see the topic...
subselect - IBM DB2 9.7 for Linux, UNIX, and Windows

You will see...

Quote:

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.

tonkuma · #5 (**permalink**) 09-09-11, 11:17

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
;

Internet Services

Web Development

Digital Marketing

Consumer Tech