Select two columns based on the same conditional statement SQL

Question

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?

This tends to quickly become vendor specific in terms of syntax - what vendor are you asking about? — Pieter Geerkens
– Pieter Geerkens, Commented Oct 13, 2014 at 15:09
Redshift is an analytical DB with the ability to optimize queries across billion of records. Storing textual data and doing string manipulation is not the best practice. Please consider using some preprocessing that will parse your data and put it to the right columns instead of the above complex and slow query. — Guy
– Guy, Commented Oct 15, 2014 at 11:27

Andrew Wolfe · Accepted Answer · 2014-10-13 16:24:22Z

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)

;

Dwayne Towell · Accepted Answer · 2014-10-13 16:40:56Z

0

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".

answered Oct 13, 2014 at 16:40

Dwayne Towell

8,6635 gold badges39 silver badges54 bronze badges

1 Comment

Sohaib Over a year ago

unfortunately redshift does not support arrays. Otherwise this was the perfect solution. docs.aws.amazon.com/redshift/latest/dg/…

Collectives™ on Stack Overflow

Select two columns based on the same conditional statement SQL

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related