Skip to Main Content
  • Questions
  • Replace hardcoded values in case statement with reference table lookup

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Manoj.

Asked: October 13, 2020 - 2:41 pm UTC

Answered by: Chris Saxon - Last updated: October 19, 2020 - 2:31 am UTC

Category: SQL - Version: 11g

Viewed 100+ times

You Asked

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,

and we said...

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.

and you rated our response

  (2 ratings)

Reviews

October 15, 2020 - 10:37 am UTC

Reviewer: Manoj N Gurnani

I would require more i/ps on how to handle not in clause condition in the mapping table.
Chris Saxon

Followup  

October 15, 2020 - 11:23 am UTC

I would require more i/ps

What are i/ps?

I guess "inputs" :D

October 16, 2020 - 7:55 am UTC

Reviewer: A reader


Connor McDonald

Followup  

October 19, 2020 - 2:31 am UTC

man I'm getting old :-)

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.