how do i switch a condition based on bind variable

Question

i have a sql query which has a condition in where clause like below:
select target_name from target_properties where host_name in
(select target_name from target_metric A 
join TARGET_MEMBERS B on A.TARGET_NAME = B.MEMBER_TARGET_NAME
where METRIC_COLUMN = 'usage' and AGGREGATE_TARGET_NAME like :oem_group)
and install_time  < (sysdate-90)

How can i make a change to this so that if the bind variable :oem_group contains 'PROD' then use the condition 'install_time  < (sysdate-90)' ,else use the condition 'install_time  < (sysdate-30)'
Im unsure of the usage of 'case' here.

Rajat · Accepted Answer

You could use a case statement
and install_time  < (case when :oem_group like '%PROD%' then (sysdate-90) else (sysdate-30) end)

If you wish to handle this using and or
where...    and (:oem_group like '%PROD%' and install_time < (sysdate-90)) 
                 or (:oem_group not like '%PROD%' and install_time < (sysdate-30))

how do i switch a condition based on bind variable

One Answer

Add your own answers!

Ask a Question