Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Inconsitent grouping when using Sum, Case, Group by in DB2

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Sravs

New User


Joined: 20 Jul 2009
Posts: 1
Location: Hyderabad

PostPosted: Wed Jun 08, 2011 8:15 pm    Post subject: Inconsitent grouping when using Sum, Case, Group by in DB2
Reply with quote

Hi,

I am trying to execute the below cursor and getting the error message 'Inconsistent Grouping'.

Code:
SELECT COL1, B.COL2,
         SUM(CASE COL5 WHEN 'AAAAA' THEN 1               
                                  ELSE 0 END ) AS COVG,
         CASE COL5 WHEN 'AAAAA' THEN COL6
                           ELSE ' ' AS FORM
FROM  TABLE1 A,
          TABLE2 B
WHERE A.COL1 = B.COL1
GROUP BY COL1, B.COL2
WITH UR
;

I want to display the value of COL6 when value in the COL5 is equal to 'AAAAA' for a particular row fetched from TABLE2.

Please help me.
Back to top
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Wed Jun 08, 2011 9:26 pm    Post subject:
Reply with quote

well that's exactly what it is.
btw, you missed an END when copying.

Code:
SELECT COL1, B.COL2,
         SUM(CASE COL5 WHEN 'AAAAA' THEN 1               
                                  ELSE 0 END ) AS COVG,
         CASE COL5 WHEN 'AAAAA' THEN COL6
                           ELSE ' ' END AS FORM
FROM  TABLE1 A,
          TABLE2 B
WHERE A.COL1 = B.COL1
GROUP BY COL1, B.COL2 ,CASE COL5 WHEN 'AAAAA' THEN COL6 ELSE ' ' END
WITH UR

will give you another result. but then the sum COVG probably isn't what you want
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts find RACF group for access to spooled... jzhardy JCL & VSAM 1 Mon May 08, 2017 11:46 am
No new posts Group Data based on a key Arun Raj DFSORT/ICETOOL 7 Thu Apr 27, 2017 11:29 pm
No new posts Need Help with : IFTHEN(WHEN=GROUP) Sumanta_89 DFSORT/ICETOOL 1 Wed Apr 19, 2017 5:23 pm
No new posts Group elements using ICETOOL Learncoholic DFSORT/ICETOOL 2 Fri Apr 14, 2017 11:01 am
No new posts Why TS/OP would Not contact site supp... RahulG31 All Other Mainframe Topics 4 Wed Mar 22, 2017 7:46 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us