CODEWITHSUNDEEP
mysqlsql
ArslanAnjum
ArslanAnjum

Reputation: 1724

multiple case statements with same logic in when

I want to use case statement for each column in mysql. The logic inside when of each case statement is same which is a long list. Is there a way to write this optimally. like

case  when cond1 
          then 'xyz'  as col1, 
               'xyz2' as col2, 
               'uuy'  as col3 
         else   null 
for all each column end.

Upvotes: 0

Views: 1501

Answers (4)

nimdil
nimdil

Reputation: 1381

Short Answer: No

Long Answer: Kind of. You can play with either wrapping the logic into function and then call it for each column (if applicable) or play with dynamic query - dirty but will work. Consider this:

create table t (
 alef varchar(100),
 bet varchar(100));

set @case := 'case when ''?'' = ''A'' then 1 else 0 end';

set @sql := concat('select ',replace(@case,'?','alef'),',',replace(@case,'?','bet'),' from t');

prepare stmt from @sql;

execute stmt;

Finally if you're really lazy ;) you can iterate through information_schema.columns view and dynamically create the select statement with s.t. along these lines:

set @subCase := '';
select @subCase := concat(@subCase,replace(@case,'?',column_name),',') from information_schema.columns where table_name = 't';

And these code can be probably safely wrapped in procedure.

Upvotes: 1

Andy N
Andy N

Reputation: 1304

You could consider using UNION ALL.

E.g.

WITH my_original_resultset AS ( ... )
SELECT
  ... AS col1,
  ... AS col2,
  ... AS col3
FROM my_original_resultset
WHERE cond1
UNION ALL
SELECT
  ... AS col1,
  ... AS col2,
  ... AS col3
FROM my_original_resultset
WHERE cond2

Depending on your use case and the indexing, this may have performance implications (good or bad), but it fits the syntax you're asking for.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270463

You need a separate case expression for each column in the result set:

select (case when cond1 then 'xyz' end) as col1, 
       (case when cond1 then 'xyz2' end) as col2, 
       (case when cond1 then 'uuy' end) as col3

A case expression returns only a single value. You can use it to conditionally put different values in a single column based on one or more conditions:

select (case when cond1 then 'xyz'
             when cond2 then 'xyz2' 
             when cond3 then 'uuy'
         end) as col

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133380

You must use separated case 

case  when cond1 then 'xyz'  as col1 end, 
      when cond1 then 'xyz2' as col2 end, 
      when cond1 then 'uuy'  as col3 end

Upvotes: 0

Related Questions