Oracle 案例

Question

这个问题在这里已经有了答案:





Oracle: merging two different queries into one, LIKE & IN

(1 个回答)


8年前关闭。




我有以下代码:

case when (a.je_source='Revaluation') then 'No_Location' 
    when d.user_name like ('SCHE%') then 'No_Location' 
    when d.user_name like ('C-FA%') then 'No_Location' 
    when d.user_name like ('C-AGO%') then 'No_Location'
    when d.user_name like ('C-VD%') then 'No_Location'
    when d.user_name like ('C-JL%') then 'No_Location'
    else d.user_name  
end as JE_User

有没有办法让它更干净？我尝试了以下操作，但收到了缺少右括号的错误。

case when (a.je_source='Revaluation') then 'No_Location'
    when d.user_name like ('SCHE%', 'C-FA%') then 'No_Location' 
    else d.user_name 
end as JE_User

Answer 1

作为一个选项，您可以使用(oracle 10g 及以上)regexp_like健康)状况:

     -- sample of data
SQL> with t1(je_source, user_name) as(
  2    select 'Revaluation1',  'SCHE123'  from dual union all
  3    select 'Revaluation2',  'C-FABCD'  from dual union all
  4    select 'Revaluation3',  'C-AGOABC' from dual union all
  5    select 'Revaluation4',  'C-VD'     from dual union all
  6    select 'Revaluation5',  'C-JLABC'  from dual union all
  7    select 'Revaluation',   'ABCDE'    from dual union all
  8    select 'Revaluation6',  'FGHIJ'    from dual
  9  ) 
 10  select je_source
 11       , user_name
 12       , case
 13           when je_source = 'Revaluation'
 14           then 'No_Location'
 15           when regexp_like(user_name, '^SCHE\w*|^C-FA\w*|^C-AGO\w*|^C-VD\w*|^C-JL\w*', 'i')
 16           then 'No_Location'
 17           else user_name
 18         end case
 19    from t1
 20  /

JE_SOURCE    USER_NAME CASE
------------ --------- -----------
Revaluation1 SCHE123   No_Location
Revaluation2 C-FABCD   No_Location
Revaluation3 C-AGOABC  No_Location
Revaluation4 C-VD      No_Location
Revaluation5 C-JLABC   No_Location
Revaluation  ABCDE     No_Location
Revaluation6 FGHIJ     FGHIJ

7 rows selected