SQL Query where clause omit if no matching records

user_2708748 0
Viewed 221

I am currently working a sql query that reads data from a complex sql view that has 20 columns. I should read the data using 8 where conditions. Among those 8 conditions, one condition is optional. Meaning, if there is no matching record that meets all those 8 conditions, omit that optional where condition and return the result.

Here is the example to explain this further::

TABLE:: EMPLOYEE_DETAILS

EMP_NO  EMP_NAME    EMP_DEPT_NO     PREF_LANGUAGE       LOCATION
------  --------    -----------     -------------       ---------
1       Name1       101             ENGLISH             USA
2       Name2       102             SPANISH             SPAIN
3       Name3       103             ENGLISH             USA 
4       Name4       102             ITALIAN             ITALY
5       Name5       103             ENGLISH             USA
6       Name6       103             ITALIAN             ITALY

QUERY:

SELECT * FROM EMPLOYEE_DETAILS
    WHERE EMP_DEPT_NO = 103 AND LOCATION ='USA' AND PREF_LANGUAGE = 'SPANISH'

In this sample sql query PREF_LANGUAGE condition is optional.
If there are records matching all the three conditions return only those matching records . As the above table has no record that matches all the three conditions, the query should omit optional condition (ie. PREF_LANGUAGE) and result the records that matches the remaining two required conditions.

Any help is highly appreciated.

Thanks

oracle sql
3 Answers

I would use exists logic here:

WITH cte AS (
    SELECT *
    FROM EMPLOYEE_DETAILS
    WHERE
        EMP_DEPT_NO = 103 AND
        LOCATION = 'USA'
)

SELECT *
FROM cte
WHERE PREF_LANGUAGE = 'SPANISH' OR
      NOT EXISTS (SELECT 1 FROM cte
                  WHERE PREF_LANGUAGE = 'SPANISH')

In plain English, the above query will return records for which the department is 103, the location the USA, and either the preferred language is Spanish or no records have this preferred language. In the latter case, then it would return records regardless of the preferred language.

user_18632290

You can use DENSE_RANK analytical function as follows:

SELECT * FROM 
(SELECT T.*,  
        DENSE_RANK() 
          OVER (ORDER BY CASE WHEN PREF_LANGUAGE = 'SPANISH' THEN 1 ELSE 2 END) AS RN
  FROM EMPLOYEE_DETAILS T
 WHERE EMP_DEPT_NO = 103 
   AND LOCATION ='USA')
WHERE RN = 1
user_115656290

I think I would use a tweak on the query like this:

WITH cte AS (
      SELECT ed.*,
             SUM(CASE WHEN PREF_LANGUAGE = 'SPANISH' THEN 1 ELSE 0 END) OVER () as NUM_PREF_SPANISH
      FROM EMPLOYEE_DETAILS ed
      WHERE EMP_DEPT_NO = 103 AND LOCATION = 'USA'
     )

SELECT *
FROM cte
WHERE PREF_LANGUAGE = 'SPANISH' OR
      NUM_PREF_LANGUAGE = 0;

The reason for phrasing it this way is that Oracle does not always materialize CTEs. This guarantees that the CTE is referenced only once, so there is no danger of the optimizer deciding to run your complex code more than once.

You can also do this in the more recent version of Oracle using a WITH TIES trick:

SELECT ed.*,                 
FROM EMPLOYEE_DETAILS ed
WHERE EMP_DEPT_NO = 103 AND LOCATION = 'USA'
ORDER BY(CASE WHEN PREF_LANGUAGE = 'SPANISH' THEN 1 ELSE 2 END)
FETCH FIRST 1 ROW WITH TIES;
user_11440350
Related