nikyojin
New User
Joined: 05 Oct 2005
Posts: 94
|
|
|
|
Hi Friends,
I need some explanation regarding the query below.
Why is the
clause in the query dependent on
.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 |
|
nikyojin
New User
Joined: 05 Oct 2005
Posts: 94
|
|
|
|
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..... |
|
