Archives of the TeradataForum

Message Posted: Thu, 17 Mar 2011 @ 20:55:25 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hello:

Can you take a look at the sql below? For the last part in the CASE statement I need to remove the join to Insurance Coverage (clm_ld_CLAIM_CVRG) because claims that are "Record Only" do not have coverage. To do that I need a subquery.

Here is the CASE statement -- and below that is the query that I need to make into a subquery.

TIA

     Sel
     c.claim_id

     ,case   when ( cvg.CVRG_OPRTNL_LINE_CD in ('PD')
             and cvg.CVRG_OPRTNL_LINE_1_CD = 'Comprehensive'
             and     c.CLAIM_TYP_DESC = 'Auto Physical Damage'
             )
             then 'Auto PD Comp'
     else    null
     end     as Auto_PD_COMP_IND

     ,case   when ( cvg.CVRG_OPRTNL_LINE_CD in ('PD')
             and cvg.CVRG_OPRTNL_LINE_1_CD = 'Collision'
             and     c.CLAIM_TYP_DESC = 'Auto Physical Damage'
             )
             then 'Auto PD Coll'
     else    null
     end     as  Auto_PD_COLL_IND

     ,case   when ( cvg.CVRG_OPRTNL_LINE_CD in ('BI', 'EL')
             and     c.CLAIM_TYP_DESC = 'Liability Automobile' )
             then 'Auto BI'
     else    null
     end     as Auto_BI_IND

     ,case   when ( cvg.CVRG_OPRTNL_LINE_CD in ('NF', 'MP')
             and     c.CLAIM_TYP_DESC = 'Liability Automobile'
             )
             then 'Auto NF / MP'
     else    null
     end     as Auto_NF_MP_IND

     ,case   when ( cvg.CVRG_OPRTNL_LINE_CD in ('PD')
             and c.CLAIM_TYP_DESC = 'Liability Automobile'
             )
             then 'Auto PDL'
     else    null
     end     as  Auto_PDL_IND

     ,case   when ( cvg.CVRG_OPRTNL_LINE_CD in ('PD')
             and cvg.CVRG_OPRTNL_LINE_1_CD = 'Glass'
             and     c.CLAIM_TYP_DESC = 'Auto Physical Damage'
             )
             then 'Auto Glass'
     else    null
     end     as  Auto_Glass_IND

     ,case   when ( cvg.CVRG_OPRTNL_LINE_CD in ('PD')
             and cvg.CVRG_OPRTNL_LINE_1_CD = 'Rent'
             and     c.CLAIM_TYP_DESC = 'Auto Physical Damage'
             )
             then 'PD Rent'
     else    null
     end     as  Auto_PD_Rent_IND

     ,case   when ( cvg.CVRG_OPRTNL_LINE_CD in ('PD')
             and cvg.CVRG_OPRTNL_LINE_1_CD = 'Towing'
             and     c.CLAIM_TYP_DESC = 'Auto Physical Damage'
             )
             then 'PD_Towing'
     else    null
     end     as  Auto_PD_Towing_IND

     ,case   when cvg.CVRG_OPRTNL_LINE_CD in ('Property')
             and c.CLAIM_TYP_CD  = 'HD'
             then 'HO_Prop_Damage'
     else    null
     end     as  HO_Prop_Damage_IND

     ,case   when c.CLAIM_TYP_CD  = 'LH'
             then 'HO_Liab'
     else    null
     end     as  HO_Liab_IND

     ,case   when c.CLAIM_TYP_CD  = 'LE'
             then 'Excess Liability'
     else    null
     end     as  Excess_Liability_IND

     ,case   when c.RECRD_ONLY_IND = 'Y'
             then 'Record Only Indicator'
     else    null
     end     as  Record_Only_IND
     --need to remove cvg join for this one

     from    PM_EDW_PRES_D.clm_ld_CLAIM_v as c
     INNER JOIN ld_DATE_v as d
             on      c.DM_EFCTV_ROW_DT LE d.CLNDR_DT
             and     c.DM_XPRTN_ROW_DT GT  d.CLNDR_DT
     INNER JOIN clm_ld_CLAIM_CVRG_v as cvg
             on      c.CLAIM_ID = cvg.CLAIM_ID
             and     cvg.DM_EFCTV_ROW_DT LE d.CLNDR_DT
             and     cvg.DM_XPRTN_ROW_DT GT  d.CLNDR_DT
     INNER JOIN clm_ld_OCCURRENCE_v as o
             on      c.OCRNC_ID = o.OCRNC_ID
             and     o.DM_XPRTN_ROW_DT = '9999-12-31'

     WHERE c.CLAIM_CRT_DT = '2010-01-25'
     and c.CLAIM_CRT_DT BETWEEN c.DM_EFCTV_ROW_DT and c.DM_XPRTN_ROW_DT and c.RGSTN_STTS_CD
     in ('03', '05', '06', '09','~') and o.RPTG_CHNL_NME in ('Core', 'Pru')
     and     o.OCRNC_ST_PRVNC_CD in (
     'AK','AL','AR','AZ','CA','CO','CT','DC','DE','FL','GA','HI','IA'
     ,'ID','IL','IN','KS','KY','LA','MA','MD','ME','MI','MN','MO','MS'
     ,'MT','NC','ND','NE','NH','NJ','NM','NV','NY','OH','OK','OR'
     ,'PA','RI','SC','SD','TN','TX','UT','VA','VT','WA','WI','WV','WY')

     order   by 1
     group   by
     c.claim_id
     ,Auto_PD_COMP_IND
     ,Auto_PD_COLL_IND
     ,Auto_BI_IND
     ,Auto_NF_MP_IND
     ,Auto_PDL_IND
     ,Auto_Glass_IND
     ,Auto_PD_Rent_IND
     ,Auto_PD_Towing_IND
     ,HO_Prop_Damage_IND
     ,HO_Liab_IND
     ,Excess_Liability_IND
     ,Record_Only_IND;



     Sel
     c.claim_id as RCRD_ONLY_CLAIMS
     from PM_EDW_PRES_D.clm_ld_CLAIM_v as c
     INNER JOIN ld_DATE_v as d
     on c.DM_EFCTV_ROW_DT LE d.CLNDR_DT
     and c.DM_XPRTN_ROW_DT GT  d.CLNDR_DT
     /*INNER JOIN clm_ld_CLAIM_CVRG_v as cvg
     on  c.CLAIM_ID = cvg.CLAIM_ID
     and cvg.DM_EFCTV_ROW_DT LE d.CLNDR_DT
     and cvg.DM_XPRTN_ROW_DT GT  d.CLNDR_DT*/ INNER JOIN clm_ld_OCCURRENCE_v as o on
     c.OCRNC_ID = o.OCRNC_ID and o.DM_XPRTN_ROW_DT = '9999-12-31'
     WHERE c.CLAIM_CRT_DT = '2010-01-25'
     and c.CLAIM_CRT_DT between c.DM_EFCTV_ROW_DT and c.DM_XPRTN_ROW_DT and c.RGSTN_STTS_CD
     not in ('07', '04') and o.RPTG_CHNL_NME in ('Core', 'Pru') and o.OCRNC_ST_PRVNC_CD in (
     'AK','AL','AR','AZ','CA','CO','CT','DC','DE','FL','GA','HI','IA'
     ,'ID','IL','IN','KS','KY','LA','MA','MD','ME','MI','MN','MO','MS'
     ,'MT','NC','ND','NE','NH','NJ','NM','NV','NY','OH','OK','OR'
     ,'PA','RI','SC','SD','TN','TX','UT','VA','VT','WA','WI','WV','WY')
     and c.RECRD_ONLY_IND = 'Y'
     order by 1
     group by
     c.claim_id;

Regards,

Mark Jahnke
Liberty Mutual
Personal Markets, Claims Technology
Data Strategy and Reporting

	<Prev	Next>		<<First	<Prev	Next>	Last>>