SQL Beginner question: CASE AS END for multiple columns?

Question

I am making a stored procedure which creates a target data table (#tmp_target_table), does some checking on it, and outputs the results in a resultset table (#tmp_resultset_table). The resultset table needs to have multiple new columns: user_warning_id, user_warning_note, and user_warning_detail in addition to existing columns from #tmp_target_table.

I have a working stored procedure as in the following but this has some issue. I need to write conditionA, conditionB, and conditionB repeatedly but these conditions will need to be changed in the future. How would you write a code that is more extensible?

<Working code>
SELECT existing_col1, existing_col2,    
CASE 
WHEN conditionA 
 THEN user_warning_id_A 
WHEN conditionB
  THEN user_warning_id_B
WHEN conditionC
  THEN user_warning_id_C
END AS user_warning_id,       
CASE 
WHEN conditionA 
  THEN user_warning_note_A 
WHEN conditionB
  THEN user_warning_note_B
WHEN conditionC
  THEN user_warning_note_C
END AS user_warning_note,       
CASE 
WHEN conditionA 
  THEN user_warning_detail_A 
WHEN conditionB
  THEN user_warning_detail_B
WHEN conditionC
  THEN user_warning_detail_C
END AS user_warning_detail, 
existing_col3, existing_col4      
INTO #tmp_resultset_table     
FROM #tmp_target_table 

SELECT * FROM #tmp_resultant_table

Answer 1

Possibly the simplest method it to move the Conditions into a sub-select, then reference a token in the other select. E.g.

SELECT  existing_col1
,       existing_col2    
,       CASE CON 
             WHEN 'A' THEN user_warning_id_A 
             WHEN 'B' THEN user_warning_id_B
             WHEN 'C' THEN user_warning_id_C     END AS user_warning_id       
,       CASE CON
             WHEN 'A' THEN user_warning_note_A 
             WHEN 'B' THEN user_warning_note_B
             WHEN 'C' THEN user_warning_note_C   END AS user_warning_note       
,       CASE CON
             WHEN 'A' THEN user_warning_detail_A 
             WHEN 'B' THEN user_warning_detail_B
             WHEN 'C' THEN user_warning_detail_C END AS user_warning_detail 
,       existing_col3
,       existing_col4        
FROM (
        SELECT  T.*
        ,       CASE WHEN conditionA THEN 'A' 
                     WHEN conditionB THEN 'B'
                     WHEN conditionC THEN 'C'     END AS CON       
        FROM
            #tmp_target_table T
    )

although Gordon's answer is also neat, even though it adds two joins in the access plan. In Db2 Syntax, this works (on Db2 11.1.3.3 anyway)

select tt.*,
       v2.user_warning_id, v2.user_warning_note, v2.user_warning_detail
from #tmp_target_table tt
,    (values (case when conditionA then 'a'
                   when conditionB then 'b'
                   when conditionC then 'c'
              end)
     ) v(cond) left join
     (values ('a', 'user_warning_id_A', 'user_warning_note_A', 'user_warning_detail_A'),
             ('b', 'user_warning_id_B', 'user_warning_note_B', 'user_warning_detail_B'),
             ('c', 'user_warning_id_C', 'user_warning_note_C', 'user_warning_detail_C')
     ) v2(cond, user_warning_id, user_warning_note, user_warning_detail)
     on v2.cond = v.cond;

testing with

create table #tmp_target_table(i int);
insert into #tmp_target_table(values 1);
create variable conditionA boolean;
create variable conditionB boolean default true;
create variable conditionC boolean;

returns

 I USER_WARNING_ID   USER_WARNING_NOTE   USER_WARNING_DETAIL
 - ----------------- ------------------- ---------------------
 1 user_warning_id_B user_warning_note_B user_warning_detail_B

Answer 2

You could put the messages into a table and the condition logic into a function.
Just using temp tables so you can test it out.

Warnings

select warningID = 1, note = 'note 1', detail = 'notes on warning 1'
into #warning
union
select warningID = 2, note = 'note 2', detail = 'notes on warning 2'
union
select warningID = 3, note = 'note 3', detail = 'notes on warning 3'
union
select warningID = 4, note = 'note 4', detail = 'notes on warning 4'

Data values that have to meet conditions coming from some table ... #conditions

select condID = 1, val1 = 10, val2 = 1
into #conditions
union
select condID = 2, val1 = 20, val2 = 1
union
select condID = 3, val1 = 5, val2 = 2
union
select condID = 4, val1 = 30, val2 = 1
union
select condID = 4, val1 = 12, val2 = 1

Then a function that determines warnings based on conditions in the data. Takes values as input and returns a warningID

create function testWarningF
(
      @val1In int
)
returns int
as
begin
declare @retVal int

select @retVal = case when @val1In <= 10 then 1
            when @val1In > 10 and @val1In <=20 then 2
            else 3
        end
return @retVal
end
go

Then, the SQL ...

select *
from #conditions c
inner join #warning w on w.warningID = dbo.warningF(val1)

... returns this result

condID  val1    val2    warningID   note    detail
1       10      1       1          note 1   notes on warning 1
2       20      1       2          note 2   notes on warning 2
3       5       2       1          note 1   notes on warning 1
4       12      1       2          note 2   notes on warning 2
4       30      1       3          note 3   notes on warning 3

Answer 3

In SQL Server, you can use a lateral join (i.e., apply) to arrange the data so you can use a reference table:

select tt.*,
       v2.user_warning_id, v2.user_warning_note, v2.user_warning_detail
from #tmp_target_table tt cross apply
     (values (case when conditionA then 'a'
                   when conditionA then 'b'
                   when conditionA then 'c'
              end)
     ) v(cond) left join
     (values ('a', user_warning_id_A, user_warning_note_A, user_warning_detail_A),
             ('b', user_warning_id_B, user_warning_note_B, user_warning_detail_B),
             ('c', user_warning_id_C, user_warning_note_C, user_warning_detail_C)
     ) v2(cond, user_warning, user_warning_note, user_warning_detail)
     on v2.cond = v.cond;

This also makes it pretty easy to add more levels, if you like.

Note: You could combine v and v2 into a single values list. I separated them, because you might want to consider making v2 an actual reference table.

EDIT:

DB2 supports lateral joins with the lateral keyword. I don't remember if DB2 supports values(). So try this:

select tt.*,
       v2.user_warning_id, v2.user_warning_note, v2.user_warning_detail
from #tmp_target_table tt cross join lateral
     (select (case when conditionA then 'a'
                   when conditionA then 'b'
                   when conditionA then 'c'
              end)
      from sysibm.sysdummy1
     ) v(cond) left join
     (select 'a' as cond, user_warning_id_A as user_warning_id, user_warning_note_A as user_warning_note, user_warning_detail_A user_warning_detail
      from sysibm.sysdummy1
      union all
      select 'b', user_warning_id_B, user_warning_note_B, user_warning_detail_B
      from sysibm.sysdummy1
      union all
      select 'c', user_warning_id_C, user_warning_note_C, user_warning_detail_C
      from sysibm.sysdummy1
     ) v2(cond, user_warning, user_warning_note, user_warning_detail)
     on v2.cond = v.cond;