Search code examples
sqlcasebetweennvl

Return a default value in SQL even if the query return no result

For a very specific need for my application, in the best scenario, I must always retrieve an additional record preceding the date filter that the user selected.

Suppose in my database I have orders with the following dates ('20220701', '20221231', '20230101', '20230701', '20240101') and the user wants all orders from 2023 (keeping in mind that I need an additional previous record).

On-screen user date filter... From: "20230101" To: "20231231"

Currently I am using the following query:

SELECT Ord1.OrderDate FROM Orders Ord1
WHERE Ord1.OrderDate BETWEEN (SELECT MAX(Ord2.OrderDate)
FROM Orders Ord2 WHERE Ord2.OrderDate < '20230101')
AND '20231231'
ORDER BY Ord1.OrderDate

Running the query I get: ('20221231', '20230101', '20230701') witch is perfect!!!

My problem is that let's assume that the user DID NOT place any order in 2022, so my database contains ('20230101', '20230701', '20240101'), at this point my query no longer works, because I do not "respect" the first condition in my BETWEEN clause, but I must still have the result ('20230101', '20230701').

Solution

  • What you want is some kind of NVL function over subqueries, like this:

    SELECT NVL(
    (SELECT MAX(Date) FROM Table1 WHERE Date < '20240101'),
    (SELECT MIN(Date) FROM Table1)
) d

    That way, if the first subquery returns NULL, the outer query will return the result of the second subquery.

    The exact syntax will depend on your DBMS. For example, in Oracle you would need "FROM DUAL" for it to be valid. COALESCE() might work if your DBMS doesn't have NVL()