Vitrum Vitrum - 1 month ago 20
SQL Question

sql oracle in() clause

Is it possible to use

in()
clause in
case.. when ...the
construction in sql query condition expression.

im my case the query is very long:

select r.insurance_package_id,r.name,
max(case r.insurance_package_id when 6 then r.name
when 7 then r.name
when 8 then r.name
when 9 then r.name
when 10 then r.name
when 11 then r.name
when 12 then r.name
when 14 then r.name
when 42 then r.name
when 44 then r.name
when 7 then r.name

end) CPI_Life


there are about 200 id supposed to be used in condition expression: thus, the query would be very brut.

Answer

It is possible .Please refer the below snippet

SELECT r.insurance_package_id
    ,r.NAME
    ,max(CASE 
            WHEN r.insurance_package_id IN (
                    6
                    ,7
                    ,8
                    ,9
                    ,10
                    ,11
                    ,12
                    ,14
                    ,42
                    ,44
                    ,7
                    )
                THEN r.NAME
            END) CPI_Life