Case statement or Where statemet

Database
Enthusiast

Case statement or Where statemet

I am trying to code in SQL Assistant something I created in SAS. I am having SAS issues that is why I am trying to use SQL Assistant instead. My code in SAS is:

[code]

%LET ASM = ('99221', '99222', '99223', '99231', '99232', '99233', '99238', '99239', '99251', '99252','99253', '99254', '99255', '99291', '518.81', '493.00', '493.00', '493.01', '493.02', '493.10', '493.11', '493.12', '493.81', '493.82', '493.90', '493.91', '493.92', '493.21', '493.20', '493.21','493.22', '496', '506.4', '277.00', '277.01', '277.02', '277.03', '277.09', '99281', '99282', '99283','99284', '99285', '492.0', '492.8', '99217', '99218', '99219', '99220', '491.20', '491.21', '491.22','518.1', '518.2', '99201', '99202', '99203', '99204', '99205', '99211', '99212', '99213', '99214','99215', '99241', '99242', '99243', '99244', '99245', '99341', '99342', '99343', '99344', '99345','99347', '99348', '99349', '99350', '99381', '99382', '99383', '99384', '99385', '99386', '99387','99391', '99392', '99393', '99394', '99395', '99396', '99397', '99401', '99402', '99403', '99404','99411', '99412', '99420', '99429', '99455', '99456', 'G0402', 'G0438', 'G0439', 'G0463');

%LET BCS = ('85.42', '85.44', '85.46', '85.48', '50', '09950', 'LT', '77055', '77056', '77057', 'G0202', 'G0204', 'G0206', '87.36', '87.37', 'RT', '19180', '19200', '19220', '19240', '19303', '19304', '19305','19306', '19307', '85.41', '85.43', '85.45', '85.47');

%LET AWC = ('99381','99382','99383','99384','99385', '99391', '99392', '99393', '99394', '99395','99461', 'G0438', 'G0439', 'V20.2', 'V20.31', 'V20.32', 'V70.0', 'V70.3', 'V70.5', 'V70.6', 'V70.8','V70.9');

%LET CCS = ('51925', '56308', '57540', '57545', '57550', '57555', '57556', '58150', '58152', '58200', '58210', '58240', '58260', '58262', '58263', '58267', '58270', '58275', '58280', '58285', '58290','58291', '58292', '58293', '58294', '58548', '58550', '58551', '58552', '58553', '58554', '58570','58571', '58572', '58573', '58951', '58953', '58954', '58956', '59135', '618.5', '752.43', 'V88.01','V88.03', '68.41', '68.49', '68.51', '68.59', '68.61', '68.69', '68.71', '68.79', '68.8', '88141','88142', '88143', '88147', '88148', '88150', '88152', '88153', '88154', '88164', '88165', '88166','88167', '88174', '88175', 'G0123', 'G0124', 'G0141', 'G0143', 'G0144', 'G0145', 'G0147', 'G0148','P3000', 'P3001', 'Q0091', '87620', '87621', '87622');

%LET CDCE = ('67028', '67030', '67031', '67036', '67039', '67040', '67041', '67042', '67043', '67101','67105', '67107', '67108', '67110', '67112', '67113', '67121', '67141', '67145', '67208', '67210','67218', '67220', '67221', '67227', '67228', '92002', '92004', '92012', '92014', '92018', '92019', '92134', '92225', '92226', '92227', '92228', '92230', '92235', '92240', '92250', '92260', '99203','99204', '99205', '99213', '99214', '99215', '99242', '99243', '99244', '99245', 'S0620', 'S0621', 'S3000','3072F', '2022F', '2024F', '2026F', 'S0625');

%LET CDCH = ('3045F', '3046F', '3044F', '83036', '83037', '3044F', '3045F', '3046F');

%LET MPM = ('80162', '80047', '80048', '80050', '80053', '80069', '82565', '82575', '80051', '84132');

%LET W34 = ('99381', '99382', '99383', '99384', '99385', '99391', '99392', '99393', '99394', '99395','99461', 'G0438', 'G0439', 'V20.2', 'V20.31', 'V20.32', 'V70.0', 'V70.3', 'V70.5', 'V70.6', 'V70.8','V70.9');

PROC SQL;connect to teradata (user='xxx' password='xxxx' TDPID=DWPROD2 connection=global MODE = Teradata);

CREATE TABLE IVRCA.Claims AS /*SELECT * FROM CONNECTION TO teradata*/select * from connection to Teradata

(SELECT B.MCID, MAX(A.CLM_NBR) AS CLAIM, C.HLTH_SRVC_CD, MAX(C.CLM_LINE_SRVC_STRT_DT) AS FIRSTDATE, MAX(C.CLM_LINE_SRVC_END_DT) AS LASTDATE, D.DIAG_CD, E.SHRT_CD_DEFN_TXT, E.LONG_CD_DEFN_TXT, A.SRVC_RNDRG_TYPE_CD,A.CLM_FORM_TYPE_CD, F.CD_VAL_NM AS FORMTYPE, G.CD_VAL_NM AS SRVCTYPE, H.CD_VAL_NM AS CLAIMSTATUS, I.ICD_PROC_CD

FROM EDW_ALLPHI.CLM A

INNER JOIN EDW_ALLPHI.MDM_PPLTN_XWALK B ON EDW_ALLPHI.CLM.MBR_KEY = EDW_ALLPHI.MDM_PPLTN_XWALK.ALT_KEY INNER JOIN EDW_ALLPHI.CLM_LINE C ON EDW_ALLPHI.CLM.CLM_ADJSTMNT_KEY = EDW_ALLPHI.CLM_LINE.CLM_ADJSTMNT_KEY INNER JOIN EDW_ALLPHI.DIAG D ON EDW_ALLPHI.CLM_LINE.PRNCPL_DIAG_CD = EDW_ALLPHI.DIAG.DIAG_CD INNER JOIN EDW_ALLPHI.HLTH_SRVC E ON EDW_ALLPHI.CLM_LINE.HLTH_SRVC_CD = EDW_ALLPPHI.HLTH_SRVC.HLTH_SRVC_CD CASE WHEN E.HLTH_SRVC_CD IN &ASM THEN 1 END AS ASM,CASE WHEN E.HLTH_SRVC_CD IN &BCS THEN 1 END AS BCS, CASE WHEN E.HLTH_SRVC_CD IN &AWC THEN 1 END AS AWC,CASE WHEN E.HLTH_SRVC_CD IN &CCS THEN 1 END AS CCS, CASE WHEN E.HLTH_SRVC_CD IN &CDCE THEN 1 END AS CDCE, CASE WHEN E.HLTH_SRVC_CD IN &CDCH THEN 1 END AS CDCH, CASE WHEN E.HLTH_SRVC_CD IN &MPM THEN 1 END AS MPM, CASE WHEN E.HLTH_SRVC_CD IN &W34 THEN 1 END AS W34

INNER JOIN EDW_ALLPHI.CLM_FORM_TYPE_CD F ON EDW_ALLPHI.CLM.CLM_FORM_TYPE_CD = EDW_ALLPHI.CLM_FORM_TYPE_CD.CLM_FORM_TYPE_CD INNER JOIN EDW_ALLPPHI.SRVC_RNDRY_TYPE_CD G ON EDW_ALLPHI.CLM.SRVC_RNDRG_TYPE_CD = EDW_ALLPHI.SRVC_RNDRG_TYPE_CD.SRVC_RNDRG_TYPE_CD INNER JOIN EDW_ALLPHI.CLM_LINE_STTS_CD H ON EDW_ALLPPHI.CLM_LINE.CLM_LINE_STTS_CD = EDW_ALLPHI.CLM_LINE_STTS_CD.CLM_LINE_STTS_CD INNER JOIN EDW_ALLPHI.CLM_ICD_PROC I ON EDW_ALLPHI.CLM.SRC_ICD_VRSN_CD = EDW_ALLPHI.CLM_ICD_PROC.SRC_ICD_VRSN_CD CASE WHEN I.ICD_PROC_CD IN &ASM THEN 1 END AS ASMB, CASE WHEN I.ICD_PROC_CD IN &BCS THEN 1 END AS BCSB, CASE WHEN I.ICD_PROC_CD IN &AWC THEN 1 END AS AWCB, CASE WHEN I.ICD_PROC_CD IN &CCS THEN 1 END AS CCSB, CASE WHEN I.ICD_PROC_CD IN &CDCE THEN 1 END AS CDCEB, CASE WHEN I.ICD_PROC_CD IN &CDCH THEN 1 END AS CDCHB, CASE WHEN I.ICD_PROC_CD IN &MPM THEN 1 END AS MPMB, CASE WHEN I.ICD_PROC_CD IN &W34 THEN 1 END AS W34B

WHERE B.MCID in (&MCID) AND ((E.HLTH_SRVC_CD IN &ASM) OR (E.HLTH_SRVC_CD IN &BCS) OR (E.HLTH_SRVC_CD IN &AWC) OR (E.HLTH_SRVC_CD IN &CCS) OR (E.HLTH_SRVC_CD IN &CDCE) OR (E.HLTH_SRVC_CD IN &CDCH) OR  (E.HLTH_SRVC_CD IN &MPM) OR (E.HLTH_SRVC_CD IN &W34) OR (I.ICD_PROC_CD IN &ASM) OR (I.ICD_PROC_CD IN &BCS) OR (I.ICD_PROC_CD IN &AWC) OR (I.ICD_PROC_CD IN &CCS) OR (I.ICD_PROC_CD IN &CDCE) OR (I.ICD_PROC_CD IN &CDCH) OR (I.ICD_PROC_CD IN &MPM) OR (I.ICD_PROC_CD IN &W34)) AND C.CLM_LINE_SRVC_STRT_DT BETWEEN '2014-10-01' AND '2015-04-20' GROUP BY

B.MCID, A.CLM_NBR, C.HLTH_SRVC_CD, C.CLM_LINE_SRVC_STRT_DT, C.CLM_LINE_SRVC_END_DT, D.DIAG_CD, E.SHRT_CD_DEFN_TXT, E.LONG_CD_DEFN_TXT, A.SRVC_RNDRG_TYPE_CD, A.CLM_FORM_TYPE_CD, F.CD_VAL_NM, G.CD_VAL_NM, H.CD_VAL_NM, I.ICD_PROC_CD )DISCONNECT FROM TERADATA; QUIT;

[/code]

In SQL Assistant I am trying this but not sure how to format the where. I tried case but not sure that is going to work for in here like it does in SAS. So thought where with then ASM and enclosing in () and including all the OR.

SQL Assistant

[code]

SELECT B.MCID, MAX(A.CLM_NBR) AS CLAIM, C.HLTH_SRVC_CD, MAX(C.CLM_LINE_SRVC_STRT_DT) AS FIRSTDATEMAX(C.CLM_LINE_SRVC_END_DT) AS LASTDATE, D.DIAG_CDE.SHRT_CD_DEFN_TXT, E.LONG_CD_DEFN_TXT, A.SRVC_RNDRG_TYPE_CD, A.CLM_FORM_TYPE_CD, F.CD_VAL_NM AS FORMTYPE, G.CD_VAL_NM AS SRVCTYPEH.CD_VAL_NM AS CLAIMSTATUS, I.ICD_PROC_CD FROM EDW_ALLPHI.CLM A INNER JOIN EDW_ALLPHI.MDM_PPLTN_XWALK B ON EDW_ALLPHI.CLM.MBR_KEY = EDW_ALLPHI.MDM_PPLTN_XWALK.ALT_KEY INNER JOIN EDW_ALLPHI.CLM_LINE C ON EDW_ALLPHI.CLM.CLM_ADJSTMNT_KEY = EDW_ALLPHI.CLM_LINE.CLM_ADJSTMNT_KEY INNER JOIN EDW_ALLPHI.DIAG D ON EDW_ALLPHI.CLM_LINE.PRNCPL_DIAG_CD = EDW_ALLPHI.DIAG.DIAG_CD INNER JOIN EDW_ALLPHI.HLTH_SRVC E ON EDW_ALLPHI.CLM_LINE.HLTH_SRVC_CD = EDW_ALLPPHI.HLTH_SRVC.HLTH_SRVC_CD INNER JOIN EDW_ALLPHI.CLM_FORM_TYPE_CD F ON EDW_ALLPHI.CLM.CLM_FORM_TYPE_CD = EDW_ALLPHI.CLM_FORM_TYPE_CD.CLM_FORM_TYPE_CD INNER JOIN EDW_ALLPPHI.SRVC_RNDRY_TYPE_CD G ON EDW_ALLPHI.CLM.SRVC_RNDRG_TYPE_CD = EDW_ALLPHI.SRVC_RNDRG_TYPE_CD.SRVC_RNDRG_TYPE_CD INNER JOIN EDW_ALLPHI.CLM_LINE_STTS_CD H ON EDW_ALLPPHI.CLM_LINE.CLM_LINE_STTS_CD = EDW_ALLPHI.CLM_LINE_STTS_CD.CLM_LINE_STTS_CD INNER JOIN EDW_ALLPHI.CLM_ICD_PROC I ON EDW_ALLPHI.CLM.SRC_ICD_VRSN_CD = EDW_ALLPHI.CLM_ICD_PROC.SRC_ICD_VRSN_CD INNER JOIN EDW_ALLPHI.CLM_ICD_PROC I ON EDW_ALLPHI.CLM.SRC_ICD_VRSN_CD = EDW_ALLPHI.CLM_ICD_PROC.SRC_ICD_VRSN_CD WHERE E.HLTH_SRVC_CD IN ((('99221', '99222', '99223', '99231', '99232', '99233', '99238', '99239', '99251', '99252', '99253', '99254', '99255', '99291', '518.81', '493.00', '493.00', '493.01', '493.02', '493.10', '493.11', '493.12', '493.81', '493.82', '493.90', '493.91', '493.92', '493.21', '493.20', '493.21', '493.22', '496', '506.4', '277.00', '277.01', '277.02', '277.03', '277.09', '99281', '99282', '99283','99284', '99285', '492.0', '492.8', '99217', '99218', '99219', '99220', '491.20', '491.21', '491.22', '518.1', '518.2', '99201', '99202', '99203', '99204', '99205', '99211', '99212', '99213', '99214', '99215', '99241', '99242', '99243', '99244', '99245', '99341', '99342', '99343', '99344', '99345', '99347', '99348', '99349', '99350', '99381', '99382', '99383', '99384', '99385', '99386', '99387', '99391', '99392', '99393', '99394', '99395', '99396', '99397', '99401', '99402', '99403', '99404', '99411', '99412', '99420', '99429', '99455', '99456', 'G0402', 'G0438', 'G0439', 'G0463') END AS ASM) OR E.HTLH_SRVC_CD IN (('85.42', '85.44', '85.46', '85.48', '50', '09950', 'LT', '77055', '77056', '77057', 'G0202', 'G0204', 'G0206', '87.36', '87.37', 'RT', '19180', '19200', '19220', '19240', '19303', '19304', '19305', '19306', '19307', '85.41', '85.43', '85.45', '85.47') END AS BCS) OR E.HLTH_SRVC_CD IN  ('99381','99382','99383','99384','99385', '99391', '99392', '99393', '99394', '99395', '99461', 'G0438', 'G0439', 'V20.2', 'V20.31', 'V20.32', 'V70.0', 'V70.3', 'V70.5', 'V70.6', 'V70.8', 'V70.9') END AS AWC) OR E.HLTH_SRVC_CD IN ( ('51925', '56308', '57540', '57545', '57550', '57555', '57556', '58150', '58152', '58200', '58210', '58240', '58260', '58262', '58263', '58267', '58270', '58275', '58280', '58285', '58290', '58291', '58292', '58293', '58294', '58548', '58550', '58551', '58552', '58553', '58554', '58570', '58571', '58572', '58573', '58951', '58953', '58954', '58956', '59135', '618.5', '752.43', 'V88.01', 'V88.03', '68.41', '68.49', '68.51', '68.59', '68.61', '68.69', '68.71', '68.79', '68.8', '88141', '88142', '88143', '88147', '88148', '88150', '88152', '88153', '88154', '88164', '88165', '88166', '88167', '88174', '88175', 'G0123', 'G0124', 'G0141', 'G0143', 'G0144', 'G0145', 'G0147', 'G0148', 'P3000', 'P3001', 'Q0091', '87620', '87621', '87622') END AS CCS) OR E.HLTH_SRVC_CD IN (('67028', '67030', '67031', '67036', '67039', '67040', '67041', '67042', '67043', '67101', '67105', '67107', '67108', '67110', '67112', '67113', '67121', '67141', '67145', '67208', '67210', '67218', '67220', '67221', '67227', '67228', '92002', '92004', '92012', '92014', '92018', '92019''92134', '92225', '92226', '92227', '92228', '92230', '92235', '92240', '92250', '92260', '99203', '99204', '99205', '99213', '99214', '99215', '99242', '99243', '99244', '99245', 'S0620', 'S0621', 'S3000', '3072F', '2022F', '2024F', '2026F', 'S0625') END AS CDCE) OR E.HLTH_SRVC_CD IN (('3045F', '3046F', '3044F', '83036', '83037', '3044F', '3045F', '3046F') END AS CDCH) OR E.HLTH_SRVC_CD IN (('80162', '80047', '80048', '80050', '80053', '80069', '82565', '82575', '80051', '84132') END AS MPM) OR E.HLTH_SRVC_CD IN (('99381', '99382', '99383', '99384', '99385', '99391', '99392', '99393', 99394', '99395', '99461', 'G0438', 'G0439', 'V20.2', 'V20.31', 'V20.32', 'V70.0', 'V70.3', 'V70.5', 'V70.6', 'V70.8', 'V70.9') END AS W34) OR I.ICD_PROC_CD IN (('99221', '99222', '99223', '99231', '99232', '99233', '99238', '99239', '99251', '99252', '99253', '99254', '99255', '99291', '518.81', '493.00', '493.00', '493.01', '493.02', '493.10''493.11', '493.12', '493.81', '493.82', '493.90', '493.91', '493.92', '493.21', '493.20', '493.21', '493.22', '496', '506.4', '277.00', '277.01', '277.02', '277.03', '277.09', '99281', '99282', '99283', '99284', '99285', '492.0', '492.8', '99217', '99218', '99219', '99220', '491.20', '491.21', '491.22', '518.1', '518.2', '99201', '99202', '99203', '99204', '99205', '99211', '99212', '99213', '99214', '99215', '99241', '99242', '99243', '99244', '99245', '99341', '99342', '99343', '99344', '99345', '99347', '99348', '99349', '99350', '99381', '99382', '99383', '99384', '99385', '99386', '99387', '99391', '99392', '99393', '99394', '99395', '99396', '99397', '99401', '99402', '99403', '99404', '99411', '99412', '99420', '99429', '99455', '99456', 'G0402', 'G0438', 'G0439', 'G0463') END AS ASMAOR I.ICD_PROC_CD IN (('85.42', '85.44', '85.46', '85.48', '50', '09950', 'LT', '77055', '77056', '77057', 'G0202', 'G0204', 'G0206', '87.36', '87.37', 'RT', '19180', '19200', '19220', '19240', '19303', '19304', '19305', '19306', '19307', '85.41', '85.43', '85.45', '85.47') END AS BCSA) OR I.ICD_PROC_CD IN (('99381','99382','99383','99384','99385', '99391', '99392', '99393', '99394', '99395', '99461', 'G0438', 'G0439', 'V20.2', 'V20.31', 'V20.32', 'V70.0', 'V70.3', 'V70.5', 'V70.6', 'V70.8', 'V70.9') END AS AWCA) OR I.ICD_PROC_CD IN ( ('51925', '56308', '57540', '57545', '57550', '57555', '57556', '58150', '58152', '58200', '58210', '58240', '58260', '58262', '58263', '58267', '58270', '58275', '58280', '58285', '58290', '58291', '58292', '58293', '58294', '58548', '58550', '58551', '58552', '58553', '58554', '58570''58571', '58572', '58573', '58951', '58953', '58954', '58956', '59135', '618.5', '752.43', 'V88.01', 'V88.03', '68.41', '68.49', '68.51', '68.59', '68.61', '68.69', '68.71', '68.79', '68.8', '88141', '88142', '88143', '88147', '88148', '88150', '88152', '88153', '88154', '88164', '88165', '88166', '88167', '88174', '88175', 'G0123', 'G0124', 'G0141', 'G0143', 'G0144', 'G0145', 'G0147', 'G0148', 'P3000', 'P3001', 'Q0091', '87620', '87621', '87622') END AS CCSA) OR I.ICD_PROC_CD IN (('67028', '67030', '67031', '67036', '67039', '67040', '67041', '67042', '67043', '67101', '67105', '67107', '67108', '67110', '67112', '67113', '67121', '67141', '67145', '67208', '67210', '67218', '67220', '67221', '67227', '67228', '92002', '92004', '92012', '92014', '92018', '92019''92134', '92225', '92226', '92227', '92228', '92230', '92235', '92240', '92250', '92260', '99203','99204', '99205', '99213', '99214', '99215', '99242', '99243', '99244', '99245', 'S0620', 'S0621', 'S3000', '3072F', '2022F', '2024F', '2026F', 'S0625') END AS CDCEA) OR I.ICD_PROC_CD IN (('3045F', '3046F', '3044F', '83036', '83037', '3044F', '3045F', '3046F') END AS CDCHA) OR I.ICD_PROC_CD IN (('80162', '80047', '80048', '80050', '80053', '80069', '82565', '82575', '80051', '84132') END AS MPMA) OR I.ICD_PROC_CD IN (('99381', '99382', '99383', '99384', '99385', '99391', '99392', '99393', '99394', '99395', '99461', 'G0438', 'G0439', 'V20.2', 'V20.31', 'V20.32', 'V70.0', 'V70.3', 'V70.5', 'V70.6', 'V70.8', 'V70.9') END AS W34A))

AND B.MCID in (

'XXXX','aaa', 'bbb', 'ccc')

AND C.CLM_LINE_SRVC_STRT_DT BETWEEN '2014-10-01' AND '2015-04-20' GROUP BY  B.MCID, A.CLM_NBR, C.HLTH_SRVC_CD, C.CLM_LINE_SRVC_STRT_DT, C.CLM_LINE_SRVC_END_DT, E.SHRT_CD_DEFN_TXT, E.LONG_CD_DEFN_TXT, A.SRVC_RNDRG_TYPE_CD, A.CLM_FORM_TYPE_CD, F.CD_VAL_NM, G.CD_VAL_NM, H.CD_VAL_NM, I.ICD_PROC_CD;

[/code]

2 REPLIES
Enthusiast

Re: Case statement or Where statemet

It looks to me like your parantheses are all funkied up in that WHERE statement. Your WHERE statement boils down to:

WHERE 
E.HLTH_SRVC_CD IN
(
((<list>) END AS ASM)
OR E.HTLH_SRVC_CD IN ((<List>) END AS BCS)
OR E.HLTH_SRVC_CD IN (<list>) END AS AWC
)

OR E.HLTH_SRVC_CD IN ((<list>) END AS CCS)
OR E.HLTH_SRVC_CD IN ((<list>) END AS CDCE)
OR E.HLTH_SRVC_CD IN ((<list>) END AS CDCH)
OR E.HLTH_SRVC_CD IN ((<list>) END AS MPM)
OR E.HLTH_SRVC_CD IN ((<list>) END AS W34)
OR I.ICD_PROC_CD IN ((<list>) END AS ASMA)
OR I.ICD_PROC_CD IN ((<list>) END AS BCSA)
OR I.ICD_PROC_CD IN ((<list>) END AS AWCA)
OR I.ICD_PROC_CD IN ((<list>) END AS CCSA)
OR I.ICD_PROC_CD IN ((<list>) END AS CDCEA)
OR I.ICD_PROC_CD IN ((<list>) END AS MPMA)
OR I.ICD_PROC_CD IN ((<list>) END AS W34A))

AND B.MCID in ('XXXX','aaa', 'bbb', 'ccc')
AND C.CLM_LINE_SRVC_STRT_DT BETWEEN '2014-10-01' AND '2015-04-20'

You can see that first bit where there are nested lists inside a list... It's just not going to work. I suspect you meant to wrap all of those OR's into a paranthetical like:

WHERE 
(
E.HLTH_SRVC_CD IN ((<list>) END AS ASM)
OR E.HTLH_SRVC_CD IN ((<list>) END AS BCS)
OR E.HLTH_SRVC_CD IN ((<list>) END AS AWC)
OR E.HLTH_SRVC_CD IN ((<list>) END AS CCS)
OR E.HLTH_SRVC_CD IN ((<list>) END AS CDCE)
OR E.HLTH_SRVC_CD IN ((<list>) END AS CDCH)
OR E.HLTH_SRVC_CD IN ((<list>) END AS MPM)
OR E.HLTH_SRVC_CD IN ((<list>) END AS W34)
OR I.ICD_PROC_CD IN ((<list>) END AS ASMA)
OR I.ICD_PROC_CD IN ((<list>) END AS BCSA)
OR I.ICD_PROC_CD IN ((<list>) END AS AWCA)
OR I.ICD_PROC_CD IN ((<list>) END AS CCSA)
OR I.ICD_PROC_CD IN ((<list>) END AS CDCEA)
OR I.ICD_PROC_CD IN ((<list>) END AS MPMA)
OR I.ICD_PROC_CD IN ((<list>) END AS W34A)
)

AND B.MCID in ('XXXX','aaa', 'bbb', 'ccc')
AND C.CLM_LINE_SRVC_STRT_DT BETWEEN '2014-10-01' AND '2015-04-20'

Furthermore there is nothing to 'END' here. Perhaps you are confusing the IN statement as a CASE statement. Instead just: 

WHERE 
(
E.HLTH_SRVC_CD IN (<list>)
OR E.HTLH_SRVC_CD IN (<list>)
OR E.HLTH_SRVC_CD IN (<list>)
OR E.HLTH_SRVC_CD IN (<list>)
OR E.HLTH_SRVC_CD IN (<list>)
OR E.HLTH_SRVC_CD IN (<list>)
OR E.HLTH_SRVC_CD IN (<list>)
OR E.HLTH_SRVC_CD IN (<list>)
OR I.ICD_PROC_CD IN (<list>)
OR I.ICD_PROC_CD IN (<list>)
OR I.ICD_PROC_CD IN (<list>)
OR I.ICD_PROC_CD IN (<list>)
OR I.ICD_PROC_CD IN (<list>)
OR I.ICD_PROC_CD IN (<list>)
OR I.ICD_PROC_CD IN (<list>)
)

AND B.MCID in ('XXXX','aaa', 'bbb', 'ccc')
AND C.CLM_LINE_SRVC_STRT_DT BETWEEN '2014-10-01' AND '2015-04-20'

Lastly, you have   "WHERE field in (<list1>) OR field in (<list2>) or field in (<list3>)".... an IN list is automatically an OR, so just combine the lists like: "WHERE field in (<list1>,<list2>,<list3>)"   It's shorter and probably a heck of a lot faster depending on the execution path Teradata takes for either form. 

Enthusiast

Re: Case statement or Where statemet

Thanks. I was hoping to have some way to single out the procedure codes and health services codes by what they are. Like the first e.hlth_srvc_cd series relates to Asthma. The 2nd is Breast Cancer, the 3rd is Adolescent Well Visits. I so wished my SAS was working because I can do all the case statements and stuff in that. I guess I will just have to do the third option and throw everything in the mix and then import the output into Access and then query to say codes A through Z are Asthma and so on.