Dynamically changing the WHERE clause

Dynamically changing the WHERE clause, without actually changing it ....
What does that mean by that ?

You may have an application, which allows the user to select on slightly different criteria. Normally, this would mean that you will need two or more WHERE clauses or that you will have to dynamically create and modify the WHERE clause. Here is a third method.

In this example, we want to allow the user to select on job, exact hiredate, hiredate greater than a value or salary in any combination.

For example all employees of a particular job with a hiredate equal to a certain date ....

SELECT * FROM emp
 WHERE (job = 'SALESMAN')
   AND (TO_CHAR(hiredate,'YYYYMMDD') = '19810220');

... or all employees with with a particular job who are have a hiredate over a certain date and have a particular salary

SELECT * FROM emp
 WHERE (job = 'SALESMAN')
   AND (TO_CHAR(hiredate,'YYYYMMDD') > '19800101')
   AND (sal = 1600);

With these four possibilities we would need 16 WHERE clauses or some fairly complex code to modify the WHERE clause !

You can avoid all this work by creating arguments to "ignore" certain criteria. In this case we will have seven criteria: :job, :ignore_job, :hiredate, :ignore_hiredate_exact, :ignore_hiredate_greather, :sal and :ignore_sal.

The SELECT statement combines each of the "ignore" arguments with the appropriate test.

SELECT * FROM emp
 WHERE (:ignore_job OR job = :job)
   AND (:ignore_hiredate_exact OR
       TO_CHAR(hiredate,'YYYYMMDD') = :hiredate)
   AND (:ignore_hiredate_greather OR
        TO_CHAR(hiredate,'YYYYMMDD') > :hiredate)
   AND (:ignore_sal OR sal = :sal);

Basically the "ignore" arguments are used as a flag. You will set the flag to TRUE if the criteria is not used. In Oracle you specify TRUE as 1 = 1 and FALSE as 1 = 0.

If you set :ignore_job to TRUE, it doesn't matter what is in job. The result will be that all jobs will be selected. If :ignore_job is to FALSE, then only those jobs that match the :job argument will be included in the result set. The logic TRUE OR anything will always be TRUE and hence, ignore the selection criteria under that OR condition - that's the trick !

Let's make a test with SQL*PLUS and Oracle, the following two SELECT statements will have exactly the same result set. The lines with 1 = 1 in the WHERE clause are simply ignored.

SELECT * FROM emp
  WHERE (1 = 1 OR job = 'SALESMAN')
    AND (1 = 1 OR TO_CHAR(hiredate,'YYYYMMDD') = '19810220')
    AND (1 = 0 OR TO_CHAR(hiredate,'YYYYMMDD') > '19820101')
    AND (1 = 1 OR sal = 1600);

SELECT * FROM emp
 WHERE (TO_CHAR(hiredate,'YYYYMMDD') > '19820101');

With this wonderful trick, you can "modify" the WHERE clause without actually changing it !