Selecting a column2 if column1 is not matching the required criteria

user_3964938 0
Viewed 40

It seems i am unable to find a efficient way to solve this problem with a oracle SQL query.

Please share your suggestions.

I have the below table.

Descr Code1 Code2 Code3 Code4 Code5
Apple A AA aa Ap Ae
Apple C AA aa Ap Ae
Apple A DD aa Ap Ae
Mango M MA mn mg mo

I would use a Query

Select Desc from myTable where Code1='A'

Above query would give me 'Apple' as the response

Select Desc from myTable where Code2='AA'

Above query would again give me 'Apple' as the response

Combining both:

Select Desc from myTable where (Code2='AA' or Code1='A')

How can i know due to which colum has the "OR" condition satisfied ?

oracle sql
1 Answers

You can test in the select:

Select t.Desc,
       (case when Code2 = 'AA' then 'Code2'
             when Code1 = 'A' then 'Code1'
        end)
from myTable t
where Code2 = 'AA' or Code1 = 'A';

If you want to see both if both match, then tweak the logic:

Select t.Desc,
       trim(',' from
               (case when Code2 = 'AA' then ',Code2' end) '||'
               (case when Code1 = 'A' then ',Code1' end)
           ) as columns
from myTable t
where Code2 = 'AA' or Code1 = 'A';

You can also use a lateral join, subquery, or CTE to define columns only once:

Select t.Desc, c.columns
from myTable t cross join lateral
     (select trim(',' from
                   (case when Code2 = 'AA' then ',Code2' end) '||'
                   (case when Code1 = 'A' then ',Code1' end)
                 ) as columns
      from dual
     ) c
where columns is not null;
user_11440350
Related