Issue with Subselect when used with Group By clause



IBM's flagship relational database management system

Issue with Subselect when used with Group By clause

Postby sikkandhar » Fri May 25, 2012 10:38 am

Hi,

Am getting sqlcode -119 (A column or expression identified in a Having clause is not valid) when i execute the below query

SELECT
T1.COL8,
T1.COL9,
COALESCE (( SELECT MAX(CHAR(T2.COL1))
                    FROM TBL_T2        T2
                   WHERE  T2.COL2   =    T1.COL5
                      AND    T2.COL3  =     T1.COL6), ' ')
FROM TBL_T1      T1
WHERE
T1.COL1 = 0              AND
T1.COL2 = 'A'            AND
T1.COL3 = 147          AND

GROUP BY T1.COL4

T2.COL1 has been defined as SMALLINT (S9(4) comp). Have used CHAR function because, i don't want it to be displayed as NULL when no row exists. I want ito be displayed as SPACES, hence i am converting into CHAR and Using COALESCE function with spaces as default values.

If i dont have Group By clause, the same query is working fine.
Could you please help me

Thanks
Sikkandhar
sikkandhar
 
Posts: 6
Joined: Tue Jan 13, 2009 1:34 pm
Has thanked: 2 times
Been thanked: 0 time

Re: Issue with Subselect when used with Group By clause

Postby NicC » Fri May 25, 2012 11:53 am

You have not selected COL4 so how can it group by that?
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 3025
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 136 times

Re: Issue with Subselect when used with Group By clause

Postby sikkandhar » Fri May 25, 2012 12:21 pm

I do Select Col 4. Forgot to mention when i was modifying the SQL to post it in forum

This is how the SQL query looks

SELECT
MAX(T1.COL8),
MAX(T1.COL9),
T1.COL4,
COALESCE (( SELECT MAX(CHAR(T2.COL1))
FROM TBL_T2 T2
WHERE T2.COL2 = T1.COL5
AND T2.COL3 = T1.COL6), ' ')
FROM TBL_T1 T1
WHERE
T1.COL1 = 0 AND
T1.COL2 = 'A' AND
T1.COL3 = 147 AND

GROUP BY T1.COL4
sikkandhar
 
Posts: 6
Joined: Tue Jan 13, 2009 1:34 pm
Has thanked: 2 times
Been thanked: 0 time

Re: Issue with Subselect when used with Group By clause

Postby GuyC » Fri May 25, 2012 3:44 pm

and you don't have an HAVING-clause, so it's pretty hard to get -119.

you select 4 values : col8,Col9,Col4 and the subselect :
there is no aggration-function on your subselect, so OR you need to add it to the group by clause , OR you need a function like MAX() SUM(),...
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: Issue with Subselect when used with Group By clause

Postby sikkandhar » Fri May 25, 2012 4:14 pm

if i use aggregate function MAX () .i.e.
MAX(COALESCE (( SELECT MAX(CHAR(T2.COL1))
FROM TBL_T2 T2
WHERE T2.COL2 = T1.COL5
AND T2.COL3 = T1.COL6), ' ')
)

then i get sqlcode -112 (The operand of an Aggregate function includes an aggregate function)

If i use GROUP BY clause for the sub select then still i get sqlcode -119.
I can't use it for the Main select as i am not selecting that column (T2.COL1)
sikkandhar
 
Posts: 6
Joined: Tue Jan 13, 2009 1:34 pm
Has thanked: 2 times
Been thanked: 0 time

Re: Issue with Subselect when used with Group By clause

Postby GuyC » Fri May 25, 2012 5:55 pm

SELECT MAX(x.COL8),
       MAX(x.COL9),
       x.COL4,
       max(x.Col_sub)
  from (select col8, col9, col4 ,
               COALESCE (( SELECT MAX(CHAR(T2.COL1))
                             FROM TBL_T2 T2
                            WHERE T2.COL2 = T1.COL5
                              AND T2.COL3 = T1.COL6), ' ') as col_sub
          FROM TBL_T1 T1
          WHERE  T1.COL1 = 0 AND
                  T1.COL2 = 'A' AND
                  T1.COL3 = 147 AND
           ) X
     GROUP BY x.COL4
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: Issue with Subselect when used with Group By clause

Postby sikkandhar » Mon May 28, 2012 11:35 am

Thanks GuyC. It worked
sikkandhar
 
Posts: 6
Joined: Tue Jan 13, 2009 1:34 pm
Has thanked: 2 times
Been thanked: 0 time


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post