I want to know which of the conditions in ORs matched, making only one request The SQL below is wrong. What is a right way to do it?

select 'MATCHED', 
case 
      WHEN flield1='xxx' THEN result=concat(result, ' field1 matched ' )
      WHEN flield2='yyy' THEN result=concat(result, ' field2 matched ' )
      WHEN flield3='zzz' THEN result=concat(result, ' field3 matched ' )
end as result 
from table 
where flield1='xxx' OR field2='yyyy' OR field3='zzzz';

I want to get result like this:

CodePudding user response：

You need a separate case expression for each condition column:

select 'MATCHED',
       concat(
         case when flield1 = 'xxx'  then 'field1 matched ' else '' end,
         case when flield2 = 'yyyy' then 'field2 matched ' else '' end,
         case when flield3 = 'zzzz' then 'field3 matched'  else '' end) as result
from table 
where flield1 = 'xxx' OR field2 = 'yyyy' OR field3 = 'zzzz';