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
 

 

Group By Clause

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

New User


Joined: 05 Oct 2005
Posts: 94

PostPosted: Tue Dec 13, 2005 2:47 pm    Post subject: Group By Clause
Reply with quote

Hi Friends,
I need some explanation regarding the query below.
Why is the
Quote:
Groupby
clause in the query dependent on
Quote:
PLATFMCD
.Instead of this I tried
Quote:
GROUP BY X.STATUSDT
,VENDOR

which didn't work out.Currently the Query is working fine since I made the changes as per the Sqlcodes I got...But I need the Actual concept behind its functioning
SELECT X.STATUSDT
,X.PLATFMCD
,(CASE
WHEN X.PLATFMCD = 'MTV'
THEN X.MVENDOR
ELSE X.CVENDOR
END) AS VENDOR
,SUM(CASE
WHEN X.REASCODE = 49
THEN 1
ELSE 0
END) AS RECEIVED
,SUM(CASE
WHEN X.REASCODE = 80 OR
X.REASCODE = 82 OR
X.REASCODE = 83
THEN 1
ELSE 0
END) AS IPEND
,SUM(CASE
WHEN X.REASCODE = 21 OR
X.REASCODE = 81
THEN 1
ELSE 0
END) AS ICAN
,SUM(CASE
WHEN X.CVENDOR = 'PSI' AND
X.REASCODE = 40
THEN 1 ELSE 0
END) AS CPSI
,SUM(CASE
WHEN X.CVENDOR = 'PRX' AND
X.REASCODE = 40
THEN 1 ELSE 0
END) AS PRX
,SUM(CASE
WHEN X.MVENDOR = 'P' AND
X.REASCODE = 40
THEN 1 ELSE 0
END) AS MPSI
,SUM(CASE
WHEN X.MVENDOR = 'D' AND
X.REASCODE = 40
THEN 1 ELSE 0
END) AS BADGER
,SUM(CASE
WHEN X.REASCODE = 22 OR
X.REASCODE = 60 OR
X.REASCODE = 61
THEN 1
ELSE 0
END) AS ACK
,SUM(CASE
WHEN X.REASCODE = 01 OR
X.REASCODE = 02 OR
X.REASCODE = 42 OR
X.REASCODE = 63
THEN 1
ELSE 0
END) AS VPEND
,SUM(CASE
WHEN X.REASCODE = 65 OR
X.REASCODE = 66 OR
X.REASCODE = 68 OR
X.REASCODE = 69
THEN 1 ELSE 0
END) AS PROGRESS
,SUM(CASE
WHEN X.REASCODE = 03 OR
X.REASCODE = 04 OR
X.REASCODE = 07 OR
X.REASCODE = 08 OR
X.REASCODE = 23 OR
X.REASCODE = 41 OR
X.REASCODE = 43 OR
X.REASCODE = 09 OR
X.REASCODE = 10 OR
X.REASCODE = 11 OR
X.REASCODE = 12 OR
X.REASCODE = 13 OR
X.REASCODE = 14 OR
X.REASCODE = 15 OR
X.REASCODE = 16 OR
X.REASCODE = 17 OR
X.REASCODE = 18 OR
X.REASCODE = 20 OR
X.REASCODE = 44 OR
X.REASCODE = 50 OR
X.REASCODE = 51 OR
X.REASCODE = 52 OR
X.REASCODE = 53 OR
X.REASCODE = 54 OR
X.REASCODE = 55 OR
X.REASCODE = 56 OR
X.REASCODE = 57 OR
X.REASCODE = 75
THEN 1
ELSE 0
END) AS CVEN
,SUM(CASE
WHEN X.REASCODE = 67
THEN 1 ELSE 0
END) AS PRINTING
,SUM(CASE
WHEN X.REASCODE = 05
THEN 1 ELSE 0
END) AS METERED
FROM
(SELECT
SUBSTR(CAS.FORMAT_ID,1,3) AS CVENDOR,
(CASE WHEN SUBSTR(MTV.MEGLI_BUS_LVL_7,1,1) = 'D'
THEN 'D'
WHEN SUBSTR(MTV.MEGLI_BUS_LVL_7,1,1) <> 'D'
AND HST.MEM_PLAT_CD = 'MTV'
THEN 'P' END) AS MVENDOR,
STT.CR_REASON_CD AS REASCODE,
HST.MEM_PLAT_CD AS PLATFMCD,
STT.CR_STATUS_DATE AS STATUSDT
FROM HUM.A_CRSTT STT,HUM.A_CRHST HST
LEFT JOIN HUM.A_CRMTV MTV ON
MTV.MMI_CONTRACT_ID = HST.MEMBER_ID AND
MTV.MMI_MEMBER_ID = HST.DEP_CD AND
MTV.CREATE_TIMESTAMP = HST.CREATE_TIMESTAMP
LEFT JOIN HUM.A_CRCAS CAS ON
CAS.MEMBER_ID = HST.MEMBER_ID AND
CAS.DEP_CD = HST.DEP_CD AND
CAS.CREATE_TIMESTAMP = HST.CREATE_TIMESTAMP
WHERE
STT.CR_MEMBER_ID = HST.MEMBER_ID AND
STT.CR_DEP_CD = HST.DEP_CD AND
STT.CR_CREATE_TIMESTMP = HST.CREATE_TIMESTAMP AND
HST.AUDIT_DATE >= (CURRENT DATE - 30 DAYS) AND
STT.CR_SEQ_NBR = (SELECT MAX(STT2.CR_SEQ_NBR)
FROM HUM.A_CRSTT STT2
WHERE STT2.CR_GROUP_NBR = HST.GROUP_NBR
AND STT2.CR_CREATE_TIMESTMP = HST.CREATE_TIMESTAMP
AND STT2.CR_MEMBER_ID = HST.MEMBER_ID
AND STT2.CR_DEP_CD = HST.DEP_CD)
) X
GROUP BY X.STATUSDT
,X.PLATFMCD
,X.CVENDOR
,X.MVENDOR
ORDER BY X.STATUSDT DESC
Back to top
View user's profile Send private message

EnjoyMF

New User


Joined: 27 May 2005
Posts: 88

PostPosted: Tue Dec 13, 2005 3:41 pm    Post subject:
Reply with quote

Hi Friend

BY syntax..........The general rule for a group by is that is shold have all the columns mentioned in the select query also in group by so X.PLATFMCD in used in the group by


in the select clause we are retrieving
X.STATUSDT
X.PLATFMCD
X.CVENDOR
X.MVENDOR

So even though we need the result only by grouping X.STATUSDT
we need to mention all of them in the query with GROUP By

Cheers...prasad
Back to top
View user's profile Send private message
nikyojin

New User


Joined: 05 Oct 2005
Posts: 94

PostPosted: Wed Dec 14, 2005 10:13 am    Post subject: Re: Group By Clause
Reply with quote

Hi Prasad,
Thanks for the reply.I'm sorry I didn't elaborate my Query.Initially in the query there was no X.PLATFMCD in the Select clause..But then the query didn't work.
The Groupby clause constituted of
GROUP BY
X.STATUSDT
,VENDOR
I had to add X.PLATFMCD later to make the query working.I need to know why it wasn't working earlier.

Thanks in advance.....
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 Syncsort Help to group fields sudhakarraju SYNCSORT 6 Thu Dec 29, 2016 1:38 am
No new posts abend sort based on count records in ... anatol DFSORT/ICETOOL 5 Mon Oct 17, 2016 10:10 pm
No new posts Get Record count in summary record fo... Atul Banke DFSORT/ICETOOL 21 Fri Sep 23, 2016 4:17 pm
No new posts Need help on SQL Dynamic WHERE Clause subratarec DB2 12 Sat Jul 16, 2016 3:11 pm
No new posts Select first 2 group data vice_versa DFSORT/ICETOOL 10 Mon Jun 13, 2016 2:21 pm


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