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
Post a Comment