set schema jemshdp52;

WITH
DISMISSALS (CASE_TYPE, DISP_CODE, DISP_DESC, DISP_TYPE) AS
(SELECT AFBDCD, AFBFCD, AFANTX, N8L5SV FROM CCADPRP 
LEFT JOIN CCBDDTP ON AFBDCD=N8BDCD AND AFBFCD=N8BFCD
WHERE N8L5SV IN ('DISATR', 'DISBTR') AND N8BDCD IN ('CV', 'EV', 'FD', 'SC')),
CASES (CASE_NUMBER, CASE_TYPE, DISP_CODE) AS 
(SELECT BIW5CF, AXBDCD, AXBFCD FROM CCACIVP LEFT JOIN CCDCSRP ON BIW5CF=AXCRCD WHERE BIS8SW='Y')
SELECT DISMISSALS.CASE_TYPE, DISMISSALS.DISP_CODE, DISMISSALS.DISP_DESC, COUNT(CASE_NUMBER) FROM DISMISSALS
LEFT JOIN CASES ON DISMISSALS.CASE_TYPE=CASES.CASE_TYPE AND
DISMISSALS.DISP_CODE=CASES.DISP_CODE
GROUP BY DISMISSALS.CASE_TYPE, DISMISSALS.DISP_CODE, DISMISSALS.DISP_DESC;