Recently I received an unusual task where I using static SQL had to either fetch all rows matching a certain value in a column or fetch the rows not matching the same value in the same column. Usually such tasks are solved by creating two cursors one for each of the two possibilities. The result is a lot of almost similar code in the program and a lot of code controlling which cursor is the active one.

It would of course be easier using one cursor processing both choices and there is in fact a general solution to this problem and similar problems in static SQL no matter how many possibilities there are to chose between. here is a simple example:

SELECT COLUMNB, COLUMNC
FROM MYTABLE
WHERE (COLUMNA = :VALUEA OR 1 = :DISABLE1)
  AND (COLUMNA <> :VALUEA OR 1 = :DISABLE2)

The hostvariable :VALUEA is set to the desired value and :DISABLE1 is set to 1, if "equal to" is to be ignored, while :DISABLE2 is set to 0 (or another value not equal to 1) in order to perform a "not equal to". Here you are able to control which predicates in the WHERE clause you want to be considered. The above method unfortunately has a huge disadvantage as it cannot exploit any indexes where COLUMNA might be included. This is of course only true for those columns which has a OR 1 = :DISABLE attached to the main predicate. In the example MYTABLE will be tablespace scanned each time no matter how the index definitions looks and what the contens of the hostvariables are.

For the above example the tablespace scan problem can be solved in the following manner if an index on COLUMNA exists:

SELECT COLUMNB, COLUMNC
FROM MYTABLE
WHERE COLUMNA BETWEEN :LOWVALUE AND :HIGHVALUE
  AND COLUMNA <> :VALUEA

When you want to perform an "equal to" then you set both :LOWVALUE and :HIGHVALUE to the same value namely the value to be returned rows for while :VALUEA is set to any value different from the desired value. To perform a "not equal to" then set :LOWVALUE to the lowest possible value for COLUMNA and :HIGHVALUE to the highest value. :VALUEA is set to the value you do not want rows returned for.

The two SQL statements above with corresponding assignment of variables deliver the same results. The last example can exploit an index and is therefore the preferred choice. The first example is more flexible and generally usable and is especially useful for smaller tables (in 2014 I will consider tables with less than one million rows for a small table) if the statement is performed a limited amount of times daily.