One of my favourite subjects in the weekly tip is the use of subselect in SQL statements. When DB2 version 8 was introduced even more possibilities were introduced. It became among a lot of other things legal to use a subselect where you can use an expression. This option makes it possible to use subselects in the select list:

SELECT (SELECT MIN(CREATOR) FROM SYSIBM.SYSTABLES)
FROM SYSIBM.SYSDUMMY1

This is a fully legal SQL statement in DB2 version 8 and later. I feel confident you will think of many creative ways of using the above. As usual there is a little drawback: when a subselect is used as an expression the table name cannot contain a location. This may be solved in a coming release of DB2.

As the title of this tip indicates I have found my own creative way of using a subselect as an expression. I have a table with versioned data. In this table there is an indication of when a row is the only version of whatever the table versions (customers, accounts, orders or ...). In this case I can optimize my subselect for retrieving the current version (consult tip week 27/2007), but only if the SQL statement must return current version for more than one occurence. If the current version of a single occurence must be retrieved the best possible SQL statement is described in tip week 32/2010. Let us take a look at a SQL statement before optimization:

SELECT keycolumn1, columna, columnb
FROM tablex x
WHERE columnd = 4
  AND keycolumn2 =
( SELECT MAX(keycolumn2)
  FROM tablex
  WHERE keycolumn1 = x.keycolumn1
)

I happen to know that columnc contains the value 'NOHIST' if there is only one row in the table for the occurence identified by keycolumn1. This knowledge I can use to perform a minor rewrite of the above SQL statement:

SELECT keycolumn1, columna, columnb
FROM tablex x
WHERE columnd = 4
  AND keycolumn2 =
CASE columnc WHEN 'NOHIST' THEN keycolumn2 ELSE
( SELECT MAX(keycolumn2)
  FROM tablex
  WHERE keycolumn1 = x.keycolumn1
) END

The CASE expression either returns the value of keycolumn2 directly or the highest value of keycolumn2 from the subselect. The subselect is only executed if columnc contains another value than 'NOHIST'. If a large part of the occurences in tablex has the value 'NOHIST' in columnc then the optimized SQL statement will perform a lot faster than before the optimization. The savings of the optimization clearly depends on the data versioning distribution.