HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

One case statement across multiple columns

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
casecolumnsstatementonemultipleacross

Problem

I have a query in which I am executing the same case statement across multiple columns, i.e. something like this

select
case when (cond) then
     'Column1 Cond T'
else'Column1 Cond F'
end,
case when (cond) then
     'Column2 Cond T'
else'Column2 Cond F'
end,
from table


I would like to "merge" these two case statement into one so I have something like this:

SELECT
 CASE WHEN (cond) then
     'Column1 Cond T', 'Column2 Cond T'
ELSE 'Column1 Cond F','Column2 Cond F'
END,
FROM table


However, I am getting a syntax error when I try running that. What is the correct syntax for this type of case statement?

Solution

Store the condition result into user variable and use it in the second CASE:

select
case when @cond := (cond) then
     'Column1 Cond T'
else'Column1 Cond F'
end,
case when @cond then
     'Column2 Cond T'
else'Column2 Cond F'
end,
from table

Code Snippets

select
case when @cond := (cond) then
     'Column1 Cond T'
else'Column1 Cond F'
end,
case when @cond then
     'Column2 Cond T'
else'Column2 Cond F'
end,
from table

Context

StackExchange Database Administrators Q#210887, answer score: 4

Revisions (0)

No revisions yet.