TransWikia.com

how do i switch a condition based on bind variable

Stack Overflow Asked by vivek on December 12, 2020

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.

One 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))

Correct answer by Rajat on December 12, 2020

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP