Coalesce with Nested Case Statement - Error3707

Database
Enthusiast

Coalesce with Nested Case Statement - Error3707

I'm trying to run the following query which was previously working wrapped in SAS hitting DB2 tables. I'm moderately aware that TD does not like Nested Case statements all that much... Is there a way to work around this? The following Query is throwing the error "expected something like a "WHEN" keyword between the 'case' keyword and the 'coalesce' keyword". Can OLAP solve this problem perhaps? THANKS!!!

create volatile table tab as (

SELECT pym.i_sys_clm AS sorce_claim_id,
clm.c_pol AS sorce_polcy_id,
pyd.i_sys_pym_det AS sorce_clm_pt_id,
cast((case coalesce((case
when pyd.c_loc = '99' then null
else pyd.c_loc
end), avy.c_loc, pyd.c_pym_det)
when '01' then 'HOSPITAL'
when '02' then (case pyd.c_pym_det
when 'FAC' then 'ASSISTED LIVING - NH'
when 'PHC' then 'ASSISTED LIVING - HC'
when 'THC' then 'ASSISTED LIVING - HC'
else 'UNKNOWN'
end)
when '03' then 'HOME CARE - THC'
when 'IHC' then 'HOME CARE - THC'
when 'THC' then 'HOME CARE - THC'
when '04' then 'NURSING HOME'
when 'FAC' then 'NURSING HOME'
when '05' then (case substr(c_pol,1,3)
when 'OAC' then 'HOME CARE - PHC - KC'
else 'HOME CARE - THC'
end)
when '06' then (case substr(c_pol,1,3)
when 'OAC' then 'HOME CARE - PHC - KC'
else 'HOME CARE - PHC'
end)
when '09' then (case substr(c_pol,1,3)
when 'OAC' then 'HOME CARE - PHC - KC'
else 'HOME CARE - PHC'
end)
when '10' then (case substr(c_pol,1,3)
when 'OAC' then 'HOME CARE - PHC - KC'
else 'HOME CARE - PHC'
end)
when '11' then (case substr(c_pol,1,3)
when 'OAC' then 'HOME CARE - PHC - KC'
else 'HOME CARE - PHC'
end)
when 'PHC' then (case substr(c_pol,1,3)
when 'OAC' then 'HOME CARE - PHC - KC'
else 'HOME CARE - PHC'
end)
when '07' then (case pyd.c_pym_det
when 'FAC' then 'RESPITE CARE - NH'
when 'PHC' then 'RESPITE CARE - HC'
when 'THC' then 'RESPITE CARE - HC'
when 'IHC' then 'RESPITE CARE - HC'
else 'UNKNOWN'
end)
when '08' then (case substr(c_pol,1,3)
when 'OAC' then 'HOME CARE - PHC - KC'
else (case pyd.c_pym_det
when 'FAC' then 'NURSING HOME'
when 'PHC' then 'HOME CARE - PHC'
when 'THC' then 'HOME CARE - THC'
end)
end)
when '12' then 'ACS Caregiver Training'
when '13' then 'ACS Equipment'
when '14' then 'ACS Special Services'
when '15' then 'ACS Other'
when '16' then 'Alt Care/rehab/non ACS'
when '17' then 'Rehabilitation Services'
when '18' then 'Physical Therapy Visits'
when '19' then 'Speech Therapy Visits'
when '20' then 'HHA/PCA'
when '21' then 'Hospice/Palliative Care'
when '22' then 'Companion'
when '23' then 'Adult Day Care'
when '24' then 'Social Worker'
when '25' then 'Nutritionist'
when '26' then 'Oxygen Therapy'
when '28' then 'Diagnostic Services'
when '29' then 'Therapeutic Therapy/Services'
when '30' then 'Pool Therapy'
when '31' then 'Massage Therapy'
when '32' then 'Holistic Services'
when '33' then 'Medication Management'
when '34' then 'Occupational Therapy Visits'
when '021' then 'HOME CARE _ HMH L1'
when '022' then 'HOME CARE _ HMH L2'
when '023' then 'NURSING HOME'
else 'Error'
end)as char(30)) as claim_loc_nm,
pyd.d_bgn_pym_det AS pmnt_from_dt,
pyd.d_end_pym_det AS pmnt_thru_dt,
pym.d_rls_pym AS pmnt_rlse_dt,
pym.c_typ_pym_clm AS pmnt_type_cd,
pyd.a_pym_det AS pmnt_amt,
(CASE
WHEN a_pym_det >= 0 THEN (DAYS(pyd.d_end_pym_det) - DAYS(pyd.d_bgn_pym_det))
ELSE ((DAYS(pyd.d_end_pym_det) - DAYS(pyd.d_bgn_pym_det)) * -1)
END) AS pmnt_days_qty

FROM prod.vltc900a_clm clm
INNER JOIN prod.vltc915a_pym pym ON clm.i_sys_clm = pym.i_sys_clm
INNER JOIN prod.vltc916a_pyd pyd ON pym.i_sys_pym = pyd.i_sys_pym
LEFT OUTER JOIN prod.vltc913a_avy avy ON pyd.i_sys_avy = avy.i_sys_avy

WHERE clm.c_pol NOT BETWEEN 'LAC000001' AND 'LAC000999' AND SorceClaimID = '387'

) with data primary index(SorceClaimID) on commit preserve rows;
3 REPLIES
Junior Contributor

Re: Coalesce with Nested Case Statement - Error3707

I don't no of any limit regarding nested CASEs (i got a script which queries a table to create 100 kilobytes of nested CASEs for the next step).

But some older Teradata releases somtimes required parenthesis around nested CASEes and COALESCE is a kind of CASE.
I did a copy&paste and it worked on my 12.00.01.07, but there was another error message.
DAYS is no valid SQL statement, luckily in Teradata it's easy to calculate the same result without it, so just remove it:

CASE
WHEN a_pym_det >= 0
THEN pyd.d_end_pym_det - pyd.d_bgn_pym_det
ELSE pyd.d_bgn_pym_det - pyd.d_end_pym_det
END) AS pmnt_days_qty

Dieter
Enthusiast

Re: Coalesce with Nested Case Statement - Error3707

So wrap the Coalesce in Parenths as well?

something like?

SELECT pym.i_sys_clm AS sorce_claim_id,
clm.c_pol AS sorce_polcy_id,
pyd.i_sys_pym_det AS sorce_clm_pt_id,
cast((case (coalesce((case
when pyd.c_loc = '99' then null
else pyd.c_loc
end)), avy.c_loc, pyd.c_pym_det)
when '01' then 'HOSPITAL'
when '02' then (case pyd.c_pym_det
when 'FAC' then 'ASSISTED LIVING - NH'
when 'PHC' then 'ASSISTED LIVING - HC'
when 'THC' then 'ASSISTED LIVING - HC'
else 'UNKNOWN'
end)
Enthusiast

Re: Coalesce with Nested Case Statement - Error3707

Got IT!

THANKS Dieter!!!!!!!!!!!