Some questions came in today from a customer’s conversion programmer who was exploring uses of various related coded columns in the system’s DB2 tables.  In this case, the quickest way was to “dive in” and run some simple count and group by queries.  For example,

select mycodecolumn1, mycodecolumn2, count(*) from mytable group by mycodecolumn1, mycodecolumn2 with ur

Sometimes it’s helpful to rollup or cube these to get sub-totals:

select mycodecolumn1, mycodecolumn2, count(*) from mytable group by rollup(mycodecolumn1, mycodecolumn2) with ur

A common problem with DB2’s standard cube implementation is that it’s hard to distinguish null values from the summary (“all”) values.  If you don’t want to see null values, simply add that to the where clause:

select mycodecolumn1, mycodecolumn2, count(*) from mytable where mycodecolumn1 is not null and mycodecolumn2 is not null group by rollup(mycodecolumn1, mycodecolumn2) with ur

If you do want to see null values, use the grouping function to distinguish:

select mycodecolumn1, mycodecolumn2, grouping(mycodecolumn1), grouping(mycodecolumn2), count(*) from mytable group by rollup(mycodecolumn1, mycodecolumn2) with ur

Zero (0) means it’s the actual column value (null or otherwise); one (1) means it’s a summary row.

Translating those coded values into meaningful descriptions is often helpful.  This can be done by joining to lookup tables (those tables that map codes to descriptions), or by using case statements.  But there are other, quicker ways.  More on that later.