SQL: Insert select statement with CASE statement.




INSERT INTO %Table(IIE_OE_TMP)(
 SELECT DISTINCT %ProcessInstance
 , PER.EMPLID
 , PER.DEPENDENT_BENEF
 , %DateOut(PLN.COVERAGE_BEGIN_DT )
 ,PLN.COVERAGE_ELECT
 ,CASE WHEN JOB.EMPL_STATUS IN ('A'
 ,'P'
 ,'S')
   AND JOB.FULL_PART_TIME = 'F' THEN 'FT' WHEN JOB.EMPL_STATUS IN ('A'
   ,'P'
   ,'S')
   AND JOB.FULL_PART_TIME = 'P' THEN 'PT' WHEN JOB.EMPL_STATUS = 'L' THEN 'L1' END
   , (CASE PER.RELATIONSHIP WHEN ' ' THEN '18' WHEN 'SP' THEN '01' WHEN 'C' THEN '19' WHEN 'NA' THEN '53' ELSE ' ' END)
   , PER.NATIONAL_ID
   , PER.LAST_NAME
   , PER.FIRST_NAME
   , PER.MIDDLE_NAME
   , PER.NAME_PREFIX
   , PER.NAME_SUFFIX
   , PER.NATIONAL_ID
   , PER.ADDRESS1
   , PER.ADDRESS2
   , PER.CITY
   , PER.STATE
   , PER.POSTAL
   , PER.COUNTRY
   , %DateOut(PER.BIRTHDATE )
   , PER.SEX
   ,PER.DISABLED
  FROM PS_BN_SNAP_PER PER
  , PS_BN_SNAP_JOB BNJOB
  , PS_JOB JOB
  ,PS_BN_SNAP_PLAN PLN
 WHERE PER.EMPLID=BNJOB.EMPLID
   AND PER.EMPLID=JOB.EMPLID
   AND PER.EMPLID=PLN.EMPLID
   AND PER.DEPENDENT_BENEF=PLN.DEPENDENT_BENEF
   AND PLN.VENDOR_ID='EYEMED001'
   AND PLN.PLAN_TYPE = '14'
   AND JOB.EMPL_STATUS IN ('A' ,'P' ,'S')
   AND JOB.EMPL_STATUS=BNJOB.EMPL_STATUS
   AND JOB.EFFDT=(
 SELECT MAX(JB.EFFDT)
  FROM PS_JOB JB
 WHERE JB.EMPLID=JOB.EMPLID
   AND JB.EMPL_RCD=JOB.EMPL_RCD
   AND JB.EFFDT<=%Bind(IIE_ANUAL_RUN_DATE))
   AND JOB.EFFSEQ=(
 SELECT MAX(J.EFFSEQ)
  FROM PS_JOB J
 WHERE J.EMPLID=JOB.EMPLID
   AND J.EMPL_RCD=JOB.EMPL_RCD
   AND J.EFFDT=JOB.EFFDT)
   AND PLN.COVERAGE_ELECT='E'
   AND PLN.COVERAGE_BEGIN_DT=(
 SELECT MAX(S1.COVERAGE_BEGIN_DT)
  FROM PS_BN_SNAP_PLAN S1
 WHERE S1.EMPLID = PLN.EMPLID
   AND S1.BENEFIT_RCD_NBR = PLN.BENEFIT_RCD_NBR
   AND S1.COBRA_EVENT_ID = PLN.COBRA_EVENT_ID
   AND S1.DEPENDENT_BENEF = PLN.DEPENDENT_BENEF
   AND S1.PLAN_TYPE = PLN.PLAN_TYPE
   AND S1.COVERAGE_BEGIN_DT <= %Bind(IIE_ANUAL_RUN_DATE)))

Comments

Popular posts from this blog

BI Publisher: If Condition with sub-string in rtf template