Using switch to select columns and filter

Question

Can we use switch case to filter the columns of a table in this way?

select distinct stud.name, stud.num,
        case WHEN stud.sub like '%data%'            THEN    stud.sub
             WHEN stud.sub like '%informatics%'     THEN    stud.sub
             WHEN stud.sub like '%genomics%'        THEN    stud.sub
                                                    ELSE    '---'
        END
from  table_A

The expected result is

Name    ID      Sub

victor  2098    -----
Lui     6754    Bioinformatics
Willis  7653    Advanceddatascience

Thanks!

Possible duplicate of [How to use SQL LIKE condition with multiple values in PostgreSQL?](http://stackoverflow.com/questions/12957993/how-to-use-sql-like-condition-with-multiple-values-in-postgresql) — Juan Carlos Oropeza, Jun 22 '16 at 14:51
The answer is Yes, You Can, And I dont see anthing wrong with your query. — Juan Carlos Oropeza, Jun 22 '16 at 14:54
There is no error but it's resulting out only one row. where as I want all the rows to be listed with "----" as stud.sub if the switch cases failed. — koolone, Jun 22 '16 at 15:01

score 0 · Answer 1 · answered Jun 22 '16 at 14:55

Yes, your query should work. If you want to try something different, this should work in Oracle. Easy to change it to any other RDBMS. Also easy to add any other subject.

With aux as ( select '%data%' as auxText from dual union all
              select '%informatics%' as auxText from dual union all            
              select '%genomics%' as auxText from dual )
select distinct stud.name, stud.num,
        nvl(stud.sub,'---') as sub
from  table_A left join aux on table_A.sub like aux.auxText;

Using switch to select columns and filter

1 Answers1