Wednesday, September 29, 2010

DB2 SQL - Some secrets

I don't know how many people know about this, but I found a neat trick when using DB2 SQL.
Usually you have to query something like this:

Now in DB2 SQL (at least it works in iSeries/AS400), you can simplify it like this:

Maybe it doesn't seem very useful, but most of the tables that I work with have a double or triple key (one being product and the other a serial number per product).
The real advantage comes when using a group of rows. Instead of using a temporary table and joining in the query, or making something horrible like:

It's much more simple to write it like this: