One of my loyal readers, Tage Gejl, is the founder of this weeks tip. It is refreshing with some input from outside. Normally I use my own experiences to generate a tip from. The moment I saw Tages idea I became wildly enthusiastic and asked him if it was alright to publish it. You are all very welcome with your ideas and I will always publish the reference unless you do not want to.

If you work on an installation that insists on using static SQL in the mainframe applications you may benefit from this tip. Static SQL prevents you from doing things that you easily could have solved using simple dynamic SQL. Often you end up have multiple static SQL statements performing the same thing with a very small variation. This may cause you a lot of pain when they have to be changed. Tage has found a smart way to have many different sort criterias in the same SQL statement:

SELECT CASE :SORTCRITERIA
       WHEN 'FIRSTNAME' THEN FIRSTNAME
       WHEN 'MIDDLENAME' THEN MIDDLENAME
       ELSE LASTNAME
       END AS SORTCOLUMN
     , FIRSTNAME, MIDDLENAME, LASTNAME, OTHERINFO
FROM MYTABLE
WHERE ...
ORDER BY SORTCOLUMN

Before you execute this SQL statement you set the hostvariable SORTCRITERIA to either 'FIRSTNAME', 'MIDDLENAME' or something else (like 'LASTNAME') and then DB2 will handle the rest. The WHERE clause is of course the same each time although it may produce different results depending on the values of the host variables included in the where clause.

The disadvantage of the above approach is that DB2 is forced to perform an internal SORT of all the rows fulfilling the criterias in the where clause. If only a few rows are returned this is no problem compared to the flexibility and maintainability of the above SQL statement. If the statement returns several thousand rows each time you may have to reconsider the method before DBA forces you to do it.