Coalesce statement to handle multiple values and NULLS?

Question

I am trying to figure out how to create an SQL query that will check for (:FROM_DATE) and (:TO_DATE) parameters and if NULL to put the past month dates in for the two values, and if not NULL to accept whatever values are entered in the parameters.

For example:

• if the user enters (01-JAN-17) as FROM_DATE, and (31-JAN-17) as TO_DATE, I want the query to not automatically pass any values for the TO_DATE and FROM_DATE.

• if the user does not enter any values for TO_DATE and FROM_DATE or there are NULL values passed in, I want the query to automatically enter the the past months values (i.e., if query is run July 1st 2017, the FROM_DATE would be 01-JUN-17 and the TO_DATE would be 30-JUN-17).

I was hinted to use a coalesce statement to handle multiple values and NULLS (i.e., AND ( (coalesce(null, :P_ORG) is null) or (ORG.ORGANIZATION_ID in :P_ORG)))???

Any help would be greatly appreciated.

Answer 1

Something like:

SELECT *
FROM   your_table
WHERE  your_date_column BETWEEN TO_DATE( :from_date, 'DD-MON-YYYY' )
                        AND     TO_DATE( :to_date, 'DD-MON-YYYY' )
OR     (   ( :from_date IS NULL OR :to_date IS NULL )
       AND your_date_column BETWEEN ADD_MONTHS( TRUNC( SYSDATE, 'MM' ), -1 )
                            AND     TRUNC( SYSDATE, 'MM' ) - 1
       );

If either (or both) :from_date or :to_date is NULL then the dates will be compared to the previous month.

If your table has dates where the time component is not always set to midnight then you will need to use:

SELECT *
FROM   your_table
WHERE  your_date_column BETWEEN TO_DATE( :from_date, 'DD-MON-YYYY' )
                        AND     TO_DATE( :to_date, 'DD-MON-YYYY' )
OR     (   ( :from_date IS NULL OR :to_date IS NULL )
       AND your_date_column >= ADD_MONTHS( TRUNC( SYSDATE, 'MM' ), -1 )
       AND your_date_column <  TRUNC( SYSDATE, 'MM' )
       );