CODEWITHSUNDEEP
sqldatabasepostgresqlamazon-redshift
Sohaib
Sohaib

Reputation: 4694

Select two columns based on the same conditional statement SQL

I have a select statement which resembles something like this

SELECT
    CASE WHEN <SOME-CONDN_1> THEN 'value1' ELSE '' ||
    CASE WHEN <SOME-CONDN_2> THEN 'value2' ELSE '' ||
    CASE WHEN <SOME-CONDN_3> THEN 'value3' ELSE '' AS value_column,
    CASE WHEN <SOME-CONDN_1> THEN 'name1' ELSE '' ||
    CASE WHEN <SOME-CONDN_2> THEN 'name2' ELSE '' ||
    CASE WHEN <SOME-CONDN_3> THEN 'name3' ELSE '' AS name_column
FROM data_table
--<REST OF THE QUERY>

The conditional statement is something like data_table.data_column ILIKE value1 and so on. Since I'm doing the same conditioning statement twice (and it involves some string matching using ILIKE) I was wondering if I could club them and make it more efficient.

Would the same be possible using SQL statements?

Upvotes: 2

Views: 3353

Answers (2)

Dwayne Towell
Dwayne Towell

Reputation: 8583

Assuming the results are both strings you can use an array to make things simpler.

SELECT a[1],a[2], ...
FROM (SELECT CASE 
        WHEN <SOME-CONDN_1> THEN ARRAY['value1','name1'] 
        WHEN <SOME-CONDN_2> THEN ARRAY['value2','name2'] 
        WHEN <SOME-CONDN_3> THEN ARRAY['value3','name3'] 
        ELSE '' AS a
    FROM ...
);

If the result values are not all the same type you can do the same thing using a ROW() constructor, but you will need to define a type in order to get the values individually "back out of the row".

Upvotes: 0

Andrew Wolfe
Andrew Wolfe

Reputation: 2096

Option 1: not quite sure if this variant of CASE works for PostgreSQL...

select case cond_num when 
    1 then 'value1'
    when 2 then 'value2',
    when 3 then 'value3' else null end as value_column,
case cond_num when 
    1 then 'name1'
    when 2 then 'name2',
    when 3 then 'name3' else null end as name_column
from (
select data_table.*,
    case when <some_condition_1> then 1 
    when <some_condition_2> then 2
    when <some_condition_3> then 3 else 0 end as cond_num
from data_table
) screened_table
;

Option 2:

select case when 
    cond1 = 1 then 'value1'
    when cond2 = 1 then 'value2',
    when cond3 = 1 then 'value3' else null end as value_column,
case when 
    cond1 = 1 then 'name1'
    when cond2 = 1 then 'name2',
    when cond3 = 1 then 'name3' else null end as name_column
from (
select data_table.*,
    case when <some_condition_1> then 1 else 0 as cond1,
    case when <some_condition_2> then 1 else 0 as cond2,
    case when <some_condition_3> then 1 else 0 as cond3
from data_table
) screened_table
;

Option 3 - note if the conditions are not exclusive may return multiple rows. Will not return rows from data_table in which no conditions are true.

select rslt.name, rslt.value
from data_table, (
    select 1 as cond, 'value1' as value, 'name1' as name
    union all
    select 2 as cond, 'value2' as value, 'name2' as name
    union all
    select 3 as cond, 'value3' as value, 'name3' as name
) rslt
WHERE (<some_condition_1> and rslt.cond = 1) OR
    (<some_condition_2> and rslt.cond = 2) OR
    (<some_condition_3> and rslt.cond = 3)

;

Upvotes: 1

Related Questions