CODEWITHSUNDEEP
sql
user836087
user836087

Reputation: 2501

Advanced SQL condition based query building

I'm trying to write a complex SQL query based on a condition. If the condition is satisfied, it should run the query with one condition, if not then another condition. How is this possible in SQL?

SELECT distinct support_id AS object_id, 2 as app_context_id,
            0 as domain_cdc_id, 'S' as object_type, 
            'NEW' as domain_cdc_status, 1 as attribute_group_num,s
            sysdate as created_on 
FROM fdrdbo.v_facility_support
WHERE support_version_valid_from_dt = to_date('${passedInVar}', 'yyyy/mm/dd')

IF ${passedInVar} is null, then I want to use the following WHERE condition instead:

WHERE SYSDATE BETWEEN support_version_valid_from_dt AND support_version_valid_to_dt

Thank you in advance.

Upvotes: 0

Views: 207

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125404

This will work in Postgresql (to_date exists in Postgresql)

where
    support_version_valid_from_dt = to_date(:passedinvar, 'yyyy/mm/dd')
    or
    (
        sysdate between support_version_valid_from_dt and support_version_valid_to_dt 
        and
        :passedinvar is null
    )

Notice that I'm assuming you are using a driver to pass the variable and not doing simple string substitution.

to_date will return null for a null argument. In Postgresql a comparison to null will return null. The second condition will only be checked if the passed variable is null.

Upvotes: 1

Related Questions