SELECT SOURCE_CODE, DATA_SOURCE, ICN, REL_LINE_NBR,
CONTRACT_NBR, MEMBER_NBR, ADJUST_INDIC,
SUM (BILLED_AMT) BILL, SUM (PAYMENT_AMT) PYMT,
SUM (WITHHOLD_AMT) WHLD, COUNT(*) CTR,
MAX (FIN_CYCLE_DT) FIN_CYCLE_DT,'1'
FROM DSNV.DWS_CLAIMS_L_MTH
GROUP BY SOURCE_CODE, DATA_SOURCE, ICN, REL_LINE_NBR,
CONTRACT_NBR, MEMBER_NBR, ADJUST_INDIC
ORDER BY SOURCE_CODE, DATA_SOURCE, ICN, REL_LINE_NBR,
CONTRACT_NBR, MEMBER_NBR, ADJUST_INDIC;
My requirement is, I want to execute the same query using SORT.
I can extract data from table using select * from DSNV.DWS_CLAIMS_L_MTH, which can be input to SORT
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
bnveena,
The following DFSORT JCL will give you the desired results.
Using an INREC we only pick the fileds we are interested. I Put all your GROUP by fields at pos 1 and we sort on the first 35 bytes which is your GROUP BY clause length and using outfil sections we feature we get the desired results.
When you have GROUP BY the ORDER BY is a mute unless your order by clause is different from the Group by.
Code:
//STEP0100 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD DSN=Your unload file,DISP=SHR
//SORTOUT DD SYSOUT=*
//SYSIN DD *
INREC BUILD=(019,02, $ SOURCE CODE
106,02, $ DATA SOURCE
003,16, $ ICN,REL LINE NBR
042,12, $ CONTRACT NUMBER
054,02, $ MEMBER NUMBER
064,01, $ ADJUST INDIC
065,06, $ FIN CYCLE DT
289,06, $ BILLED AMT
295,06, $ PAYMENT AMT
302,06) $ WITHHOLD AMT