Every Bucket Deserves a Descriptive Name
July 28, 2010 Hey, Guru
|
One of the fun things about editing this newsletter is reading the interesting ideas I get from you, the readers. One reader in particular, one of the several David’s from whom I hear, comes up with interesting questions, comments and ideas. Here’s an SQL tip he came up with. I thought I’d share it with you. Many of us use files with “buckets” (a list of repeating fields), in order to easily present data to users in an intuitive format. A file with repeating fields violates First Normal Form (1NF), and can cause grief when the number of buckets must be changed, but in general this use of “abnormalized” data is effective. Here’s a file of sales order information:
create table somelib/Orders
( Item char (6), Bucket01 dec (3,0),
Bucket02 dec (3,0), Bucket03 dec (3,0),
Bucket04 dec (3,0), Bucket05 dec (3,0) )
And here’s what the data might look like: ITEM BUCKET01 BUCKET02 BUCKET03 BUCKET04 BUCKET05 AB-190 17 38 60 14 21 HT-821 0 0 4 0 1 YC-254 1 2 0 7 9 David didn’t think this was very descriptive, so he suggested using LABEL ON to describe the columns:
label on column orders
(BUCKET01 is '07/26',
BUCKET02 is '07/27',
BUCKET03 is '07/28',
BUCKET04 is '07/29',
BUCKET05 is '07/30')
The buckets are described as dates: month and day. Now the data looks like this instead: ITEM 07/26 07/27 07/28 07/29 07/30 AB-190 17 38 60 14 21 HT-821 0 0 4 0 1 YC-254 1 2 0 7 9 Be aware that this may not work in all interfaces. In green-screen interactive SQL and Query for i (or whatever they call it these days), I see the month/day column headings. But in PC-based SQL clients, I see the field names. Also, LABEL ON doesn’t work for tables defined with DECLARE GLOBAL TEMPORARY TABLE. But it does work for physical files created from DDS. Go figure. –Ted
|
