Use Field Names for Column Headings
May 11, 2005 Ted Holt
SQL/400 SELECT statements use the column headings that are stored as part of the file description when displaying or printing data. Sometimes I prefer to see the field names themselves used as column headings. I have found one method to make SQL use field names as column headings, but I wonder if you know of a better one.
Danny is referring to files that are created with DDS, although column headings may also be defined with SQL’s LABEL ON command. Here’s an example of a few fields with defined column headings.
A UNIQUE A R CUSTREC A COMPANY 3P 0B TEXT('Company') A DFT(1) A COLHDG(' ' ' ' 'Company') A EDTCDE(1) A CUSTNBR 5P 0B TEXT('Customer account number') A COLHDG('Customer' ' account' + A ' number') A EDTCDE(4) A CUSTNAME 20 B TEXT('Customer name') A COLHDG(' ' 'Customer' 'name') A ALWNULL
Here’s a simple select against those fields.
SELECT COMPANY, CUSTNBR, CUSTNAME FROM SOMEFILE
Selecting these fields shows output like the following:
Customer account Customer Company number name 1 20304 BIL'S GROCERY 1 30405 TOM'S PHARMACY
Danny’s method is to rename each field with its own name.
SELECT COMPANY AS COMPANY, CUSTNBR AS CUSTNBR, CUSTNAME AS CUSTNAME FROM SOMEFILE
Renaming in this manner produces the following output.
COMPANY CUSTNBR CUSTNAME 1 20304 BIL'S GROCERY 1 30405 TOM'S PHARMACY
I don’t know of any setting that tells SQL to use field names for column headings, so I do not have a better method than the one Danny is using.
SQL/400 also picks up other formatting options from the file description. I have found it helpful to define edit codes and edit words in DDS, so that queried data is formatted nicely. For example, over an eight-digit, numeric field that contains a date in YYYYMMDD format, the following edit word is helpful.
EDTWRD(' / / ')
There are four blanks before the first slash for the year, two blanks between the slashes for the month, and two blanks after the second slash for the day. The value 20051231 displays as 2005/12/31.