Alternate SQL Row-Selection Criteria, Take 2

Hey, Ted:

Here is an alternative to the solution described in "Alternate SQL Row-Selection Criteria."

You can use a CASE statement in the WHERE clause. This has the added benefit of being able to select based on multiple criteria instead of just the single criteria, like in the example you provided. The SQL statement would end up looking something like this:

SELECT cusno, cusname, cuszip, cusclass 
       FROM customers 
      WHERE 
            (Case when :iClass <> '' 
            then :iClass 
            else cusclass 
            End) = cusclass 
        AND 
            (Case when :iFromZip <> '' 
            then substr(cuszip,1,5) 
            else :iFromZip 
            End) between :iFromZip and :iThruZip 

I have used this technique extensively. Informal performance testing has shown that a few CASE statements don't seem to noticeably hurt performance. I'm not sure how performance would hold up as the number of CASE statements increased.

The SELECT statement won't work if you omit ELSE, like this:

      WHERE 
            (Case when :iClass <> '' 
            then :iClass 
            End) = cusclass 

--Mark

Thanks for writing, Mark. I've never used CASE in the WHERE clause.

In the first CASE of your example, iClass is compared to blanks. If it is not blank, its value is compared to the CUSCLASS field, selecting only the records with that class; otherwise, the CUSCLASS field is compared to itself, selecting all records regardless of class.

The second CASE structure appears to be different, but it is really the same logic. The difference is that the result of the case is compared with host variables, not a database field.

In my testing, I discovered a couple of things to be aware of when using this technique. First, it's not possible to test for a blank value; that is, you can't select the customers whose class field is blank. However, I understand that limitation, as I often allow record selection for non-blank values only in my programming.

Second, your logic assumes that the fields cannot have null values. For example, suppose the CUSCLASS field permits the null value and that you run a query with a blank iClass variable and non-blank iFromZip and IThruZip variables. Any records that have a null CUSCLASS will not be selected, even if the ZIP code falls in the selected range. That's because the first CASE will compare a null with a null, and the test will fail. I know it sounds strange, but null does not equal null. This is the reason why your technique fails when you omit the ELSE clause. An unmatched CASE expression with no ELSE clause returns a null value, which does not match anything.

I had to alter the first CASE statement slightly.

(Case when :iCusType <> '' 
then :iCusType             
else ifnull(CusType,' ')   
End) = ifnull(CusType,' ')

The IFNULL function converts nulls to blanks in this example, which causes records with null customer types to match themselves.

Thanks for teaching me something new, Mark!

--Ted

Sponsored By ADVANCED SYSTEMS CONCEPTS

E-Mail Report and File Distribution for the iSeries & AS/400 - ESEND - - Saves paper and shipping costs - - Eliminates report distribution hassles - - Improves information access - - Archives important information - - Saves YOUR time - Get a Free Trial today! Read More