Oracle 用参考表查找替换case语句中的硬编码值

我需要将sql中使用的下面case语句中的hardoddings转换为查找引用表。
案例语句涉及4列，即企业、pbm_vendor、bhc_relationship_type、payer_entity
我想创建一个查找表，使用这4列作为不同的值和一个额外的列说regional_payer返回值。
我面临的问题，由于不同的条件是每个when子句和使用like运算符。
请协助。

 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,

您可以存储您想要比较的值，因为您目前在您的like条件-使用通配符。然后加入列像新的一样的地方。

例如:

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  

不过，你必须小心处理这件事。

目前，您的条件不是互斥的-例如，一行可能有 “哈佛朝圣者” 的企业和 “佛罗里达蓝” 的玩家实体。符合两个案例条件。case表达式将仅与其中之一匹配 (列表中的第一个)。

但是当你加入时，你会得到两行。解决这个问题可能会变得复杂，特别是如果你允许它之外的人编辑映射。

解决不在状态也很棘手-您需要仔细编写和测试联接，以确保只匹配一行。

您可能最好只使用映射表进行精确匹配 (enterprise = '...')，使用案例表达式进行更复杂的标准。