I've a requirement to convert hardocdings in below case statement used in sql to a lookup reference table .
The case statement refers to 4 columns i.e enterprise,pbm_vendor,bhc_relationship_type,payer_entity
I would like to create a lookup table using these 4 columns as distinct values and an additional column say regional_payer to return the value.
I'm facing issues due to different conditions is the each when clause and the use of like operator.
Kindly assist.
CASE
WHEN CLM.ENTERPRISE = 'Harvard Pilgrim' THEN 'Harvard Pilgrim'
WHEN CLM.ENTERPRISE = 'Blue Cross Blue Shield of Massachusetts' THEN 'Blue Cross Blue Shield of Massachusetts'
WHEN CLM.ENTERPRISE = 'Blue Cross & Blue Shield of Rhode Island' THEN 'Blue Cross & Blue Shield of Rhode Island'
WHEN CLM.ENTERPRISE = 'Tufts Health Plan' THEN 'Tufts Health Plan'
WHEN CLM.ENTERPRISE = 'Blue Cross Blue Shield of Michigan' THEN 'Blue Cross Blue Shield of Michigan'
WHEN CLM.ENTERPRISE = 'Health Delegates' THEN 'Health Delegates'
WHEN CLM.ENTERPRISE = 'Highmark Inc.' THEN 'Highmark Inc.'
WHEN CLM.ENTERPRISE = 'Spectrum / Priority Health' THEN 'Spectrum / Priority Health'
WHEN CLM.ENTERPRISE = 'UPMC Health Plan' THEN 'UPMC Health Plan'
WHEN CLM.ENTERPRISE = 'Independence Blue Cross' THEN 'Independence Blue Cross'
WHEN CLM.ENTERPRISE = 'Geisinger Health Plan' THEN 'Geisinger Health Plan'
WHEN CLM.ENTERPRISE = 'Capital BlueCross' THEN 'Capital BlueCross'
WHEN CLM.ENTERPRISE = 'Horizon BlueCross BlueShield' THEN 'Horizon BlueCross BlueShield'
WHEN CLM.ENTERPRISE = 'Excellus BlueCross BlueShield' THEN 'Excellus BlueCross BlueShield'
WHEN CLM.ENTERPRISE = 'EmblemHealth' THEN 'EmblemHealth'
WHEN CLM.ENTERPRISE = 'Independent Health' THEN 'Independent Health'
WHEN CLM.ENTERPRISE = 'HealthNow New York' THEN 'HealthNow New York'
WHEN CLM.ENTERPRISE = 'MVP Healthcare' THEN 'MVP Healthcare'
WHEN CLM.ENTERPRISE = 'Federal Employee Program' THEN 'Federal Employee Program'
WHEN CLM.ENTERPRISE = 'Blue Cross and Blue Shield of North Carolina' THEN 'Blue Cross and Blue Shield of North Carolina'
WHEN CLM.ENTERPRISE = 'BlueCross BlueShield of South Carolina' THEN 'BlueCross BlueShield of South Carolina'
WHEN CLM.ENTERPRISE = 'Carefirst BlueCross BlueShield' THEN 'Carefirst BlueCross BlueShield'
WHEN CLM.ENTERPRISE = 'Anthem' AND UPPER(CLM.PAYER_ENTITY) LIKE '%NORTH CAROLINA%' THEN 'Anthem(NC, SC, MD, VA, WV)'
WHEN CLM.ENTERPRISE = 'Anthem' AND UPPER(CLM.PAYER_ENTITY) LIKE '%SOUTH CAROLINA%' THEN 'Anthem(NC, SC, MD, VA, WV)'
WHEN CLM.ENTERPRISE = 'Anthem' AND UPPER(CLM.PAYER_ENTITY) LIKE '%MARYLAND%' THEN 'Anthem(NC, SC, MD, VA, WV)'
WHEN CLM.ENTERPRISE = 'Anthem' AND UPPER(CLM.PAYER_ENTITY) LIKE '%VIRGINIA%' THEN 'Anthem(NC, SC, MD, VA, WV)'
WHEN CLM.ENTERPRISE = 'Anthem' AND UPPER(CLM.PAYER_ENTITY) LIKE '%WEST VIRGINIA%' THEN 'Anthem(NC, SC, MD, VA, WV)'
WHEN CLM.PAYER_ENTITY = 'Florida Blue' THEN 'Florida Blue'
WHEN CLM.ENTERPRISE = 'Anthem' AND CLM.PAYER_ENTITY LIKE '%Georgia%' THEN 'Anthem Blue Cross and Blue Shield of Georgia'
WHEN CLM.PBM_VENDOR = 'ProCare Rx' THEN 'ProCare'
WHEN CLM.ENTERPRISE = 'BlueCross BlueShield of Tennessee' THEN 'BlueCross BlueShield of Tennessee'
WHEN CLM.ENTERPRISE = 'Blue Cross Blue Shield of Arkansas' THEN 'Blue Cross Blue Shield of Arkansas'
WHEN CLM.ENTERPRISE = 'Blue Cross and Blue Shield of Alabama' THEN 'Blue Cross and Blue Shield of Alabama'
WHEN CLM.ENTERPRISE = 'Blue Cross & Blue Shield of Mississippi' THEN 'Blue Cross & Blue Shield of Mississippi'
WHEN CLM.PAYER_ENTITY LIKE '%Public Education Employee Health Plan%' THEN 'PEEHIP'
WHEN CLM.ENTERPRISE = 'Premera Blue Cross' THEN 'Premera Blue Cross'
WHEN CLM.ENTERPRISE = 'Cambia (Regence)' THEN 'Cambia (Regence)'
WHEN CLM.ENTERPRISE = 'PacificSource' THEN 'PacificSource'
WHEN CLM.ENTERPRISE = 'Moda Health' THEN 'Moda Health'
WHEN CLM.ENTERPRISE = 'Centene' THEN 'Centene'
WHEN CLM.ENTERPRISE = 'Blue Cross of Idaho' THEN 'Blue Cross of Idaho'
WHEN CLM.PBM_VENDOR = 'Envision Rx' THEN 'Envision Rx'
WHEN CLM.ENTERPRISE not in ('Kaiser Permanente') AND CLM.PBM_VENDOR = 'MedImpact' THEN 'MedImpact'
WHEN CLM.PAYER_ENTITY = 'Anthem Blue Cross of California' THEN 'Anthem Blue Cross of California'
WHEN CLM.ENTERPRISE = 'Blue Shield of California' THEN 'Blue Shield of California'
WHEN CLM.ENTERPRISE = 'Blue Cross Blue Shield of Hawaii (HMSA)' THEN 'Blue Cross Blue Shield of Hawaii (HMSA)'
WHEN CLM.ENTERPRISE = 'Scan Health Plan' THEN 'Scan Health Plan'
WHEN CLM.ENTERPRISE = 'Blue Cross Blue Shield of Arizona' THEN 'Blue Cross Blue Shield of Arizona'
WHEN CLM.PAYER_ENTITY LIKE '%PEHP%' THEN 'PEHP'
WHEN CLM.ENTERPRISE = 'Blue Cross and Blue Shield of Nebraska' THEN 'Blue Cross and Blue Shield of Nebraska'
WHEN CLM.ENTERPRISE IN ('Blue Cross and Blue Shield of Kansas', 'Blue Cross and Blue Shield of Kansas City') THEN 'Blue Cross and Blue Shield of Kansas'
WHEN CLM.PAYER_ENTITY = 'UHC / Health Plan of Nevada (NV)' THEN 'Health Plan of Nevada (NV)'
WHEN CLM.ENTERPRISE = 'Health Care Service Corporation (HCSC)' THEN 'Health Care Service Corporation (HCSC)- Mid West'
WHEN CLM.ENTERPRISE = 'Blue Cross and Blue Shield of Minnesota' THEN 'Blue Cross and Blue Shield of Minnesota'
WHEN CLM.ENTERPRISE = 'Health Alliance (HAMP)' THEN 'Health Alliance (HAMP)'
WHEN CLM.ENTERPRISE = 'Navitus' THEN 'Navitus'
WHEN CLM.ENTERPRISE = 'Wellmark BlueCross BlueShield' THEN 'Wellmark BlueCross BlueShield'
WHEN CLM.ENTERPRISE = 'Health Care Service Corporation (HCSC)' AND CLM.PAYER_ENTITY LIKE '%Texas%' THEN 'Health Care Service Corporation (HCSC) - Texas'
WHEN CLM.ENTERPRISE = 'Health Care Service Corporation (HCSC)' AND CLM.PAYER_ENTITY LIKE '%New Mexico%' THEN 'Health Care Service Corporation (HCSC) - New Mexico'
WHEN CLM.ENTERPRISE = 'Blue Cross and Blue Shield of Louisiana' THEN 'Blue Cross and Blue Shield of Louisiana'
WHEN CLM.ENTERPRISE = 'Federal Employee Program' AND CLM.MM_REGION = 'New England' THEN 'Federal Employee Program - New England'
WHEN CLM.ENTERPRISE = 'Federal Employee Program' AND CLM.MM_REGION = 'Great Lakes' THEN 'Federal Employee Program - Great Lakes'
WHEN CLM.ENTERPRISE = 'Federal Employee Program' AND CLM.MM_REGION = 'Northeast' THEN 'Federal Employee Program - Northeast'
WHEN CLM.ENTERPRISE = 'Federal Employee Program' AND CLM.MM_REGION = 'Mid Atlantic' THEN 'Federal Employee Program - Mid Atlantic'
WHEN CLM.ENTERPRISE = 'Federal Employee Program' AND CLM.MM_REGION = 'Southeast' THEN 'Federal Employee Program - Southeast'
WHEN CLM.ENTERPRISE = 'Federal Employee Program' AND CLM.MM_REGION = 'Mississippi Valley' THEN 'Federal Employee Program - Mississippi Valley'
WHEN CLM.ENTERPRISE = 'Federal Employee Program' AND CLM.MM_REGION = 'NorthWest' THEN 'Federal Employee Program - NorthWest'
WHEN CLM.ENTERPRISE = 'Federal Employee Program' AND CLM.MM_REGION = 'Southwest' THEN 'Federal Employee Program - Southwest'
WHEN CLM.ENTERPRISE = 'Federal Employee Program' AND CLM.MM_REGION = 'Rocky Mountain' THEN 'Federal Employee Program - Rocky Mountain'
WHEN CLM.ENTERPRISE = 'Federal Employee Program' AND CLM.MM_REGION = 'Mid West' THEN 'Federal Employee Program - Mid West'
WHEN CLM.ENTERPRISE = 'Federal Employee Program' AND CLM.MM_REGION = 'South Central' THEN 'Federal Employee Program - South Central'
WHEN CLM.ENTERPRISE LIKE '%Centene%' AND CLM.PAYER_ENTITY LIKE '%Health Net%' THEN 'Health Net'
WHEN CLM.ENTERPRISE LIKE '%Centene%' AND CLM.PAYER_ENTITY LIKE '%Envolve%' THEN 'Envolve'
WHEN CLM.ENTERPRISE = 'Cigna / HealthSpring' THEN 'Cigna / HealthSpring - Mississippi Valley'
WHEN CLM.ENTERPRISE = 'Cigna / HealthSpring' THEN 'Cigna / HealthSpring - South Central'
WHEN CLM.ENTERPRISE = 'Molina' THEN 'Molina - Southwest'
WHEN CLM.ENTERPRISE LIKE '%Sharp Health Plan%' AND CLM.PBM_VENDOR = 'MedImpact' THEN 'Sharp Health Plan'
WHEN CLM.ENTERPRISE LIKE '%HealthPartners%' THEN 'HealthPartners'
WHEN CLM.PAYER_ENTITY LIKE '%State of Louisiana / Office of Group Benefits%' THEN 'Office of Group Benefits'
ELSE NULL
END AS REGIONAL_PAYER,
You can store the value you want to compare on as you currently have it in your like conditions - with the wildcards. Then join where the column is like the new.
For example:
create table t (
c1 varchar2(10)
);
create table mapping_t (
c1 varchar2(10),
c2 varchar2(20)
);
insert into t values ( 'EXACT' );
insert into t values ( 'LLIKEE' );
insert into mapping_t values ( 'EXACT', 'Exact mapping' );
insert into mapping_t values ( '%LIKE%', 'Like mapping' );
select t.c1, c2 from t
join mapping_t
on t.c1 like mapping_t.c1;
C1 C2
EXACT Exact mapping
LLIKEE Like mapping
You'll have to tread carefully with this though.
Currrently your conditions are not mutally exclusive - e.g. a row could have an enterprise of 'Harvard Pilgrim' and a player entity of 'Florida Blue'. Which matches two of the case conditions. The case expression will only match to one of these (the first in the list).
But when you join you'll get both rows. Resolving this could get complicated, particularly if you allow people outside IT to edit the mappings.
Resolving the NOT IN condition will be tricky too - you'll need to write and test the join carefully to ensure you only match one row.
You may be better off only using the mapping table for exact matches (enterprise = '...'), using the case expression for the more complicated criteria.