sql Case condition for multiple columns-CodePudding

I have created a query to get below columns as output

SELECT person_number,
       second_year_ltp,
       second_year_salary,
       one_year_bonus,
       one_year_salary,
       one_year_ltp,
       third_year_bonus,
       three_year_ltp,
       three_year_salary,
       current_ltp,
       current_salary,
       current_bonus,
       current_year,
       previous_year,
       second_year,
       third_year,
       fourth_year
FROM   comp_sal

I want to change values of multiple columns if the sysdate >= '01-04-2022', I cannot write the below code as it will give an error -

select person_number,
 CASE
              WHEN to_char(sysdate,'dd-mm-yyyy') >= '01-04-2022'
              THEN
                    current_bonus        one_year_bonus,
                     current_ltip       one_year_ltp,
                     current_salary     one_year_salary,
                     one_year_bonus     second_year_bonus,
                     one_year_ltp     second_year_ltp,
                     one_year_salary    second_year_salary,
                     second_year_bonus  third_year_bonus,
                     second_year_ltp   three_year_ltp,
                     second_year_salary three_year_salary
                     
                     else 
                     second_year_ltp,
                     second_year_salary,
                     one_year_bonus,
                     one_year_salary,
                     one_year_ltp,
                     third_year_bonus,
                     three_year_ltp,
                     three_year_salary,
                     current_ltip,
                     current_salary
                     end 
                     from comp_sal

Will I have to write the case when for each column ? is there a better way to do it ?

CodePudding user response：

You need to write one case statement per column.

select person_number,
 (CASE WHEN to_char(sysdate,'dd-mm-yyyy') >= '01-04-2022' THEN current_ltip       ELSE second_year_ltp END) one_year_bonus,
 (CASE WHEN to_char(sysdate,'dd-mm-yyyy') >= '01-04-2022' THEN current_salary       ELSE second_year_salary END) one_year_ltp,
 (CASE WHEN to_char(sysdate,'dd-mm-yyyy') >= '01-04-2022' THEN one_year_salary       ELSE one_year_bonus END) one_year_salary
from comp_sal

Or you can write two query then combine both with union all. Number of columns in both query should match in appropriate order.

SELECT               person_number,
                     current_bonus        one_year_bonus,
                     current_ltip       one_year_ltp,
                     current_salary     one_year_salary,
                     one_year_bonus     second_year_bonus,
                     one_year_ltp     second_year_ltp,
                     one_year_salary    second_year_salary,
                     second_year_bonus  third_year_bonus,
                     second_year_ltp   three_year_ltp,
                     second_year_salary three_year_salary
                     
from comp_sal where to_char(sysdate,'dd-mm-yyyy') >= '01-04-2022'
union all
select               person_number,                     
                     second_year_ltp,
                     second_year_salary,
                     one_year_bonus,
                     one_year_salary,
                     one_year_ltp,
                     third_year_bonus,
                     three_year_ltp,
                     three_year_salary,
                     current_ltip                                        
from comp_sal where to_char(sysdate,'dd-mm-yyyy') < '01-04-2022'

CodePudding user response：

Yes, you have write a separate case expression for each column. Alternatively, you can write two separate SQL statements, one with your condition in the where clause, and the other has the inverse of it, and combine the two queries using union all

CodePudding user response：

If it is oracle, As an alternative, you can write this as pl/sql block as follows:

declare
v_query varchar2(4000);
begin
 if to_char(sysdate,'dd-mm-yyyy') >= '01-04-2022' then
-- select one set of columns
v_query := 'SELECT person_number,
                     current_bonus        one_year_bonus,
                     current_ltip       one_year_ltp,
                     current_salary     one_year_salary,
                     one_year_bonus     second_year_bonus,
                     one_year_ltp     second_year_ltp,
                     one_year_salary    second_year_salary,
                     second_year_bonus  third_year_bonus,
                     second_year_ltp   three_year_ltp,
                     second_year_salary three_year_salary ';
else
-- select another set of columns
v_query := 'SELECT person_number, second_year_ltp,
                     second_year_salary,
                     one_year_bonus,
                     one_year_salary,
                     one_year_ltp,
                     third_year_bonus,
                     three_year_ltp,
                     three_year_salary,
                     current_ltip,
                     current_salary ';
end if;
 -- CONCAT FROM CLAUSE IN THE QUERY
v_auery := v_query ||' FROM comp_sal ';

-- run the query and store the result set in a table
execute immediate 'CREATE TABLE result_set as '||v_query;

-- your final result will be in the table result_set
end;