Changing column headings in output file

I am sure there are some who are going to criticize me for saying this, but I do use Query. While I do not use it for "production" reports, I do use it is a quick and easy way to check data, or even to generate a quick "one off" output file. Yes, I know I could use SQL to do the same, but sometimes it is just, in my opinion, quicker and easier to use Query.

One of the drawbacks of using Query are the Column Headings it produces for outfiles. If for example I join a file to itself and/or "define a result field" the Column Heading produced is unclear or outright confusing. Even if I go into the option to "Specify report column formatting" and change the Column Headings, I still get the original column headings in the outfile.

I could email myself/download the file and change the column headings in Microsoft Excel, but there must be an easy way to do it? Fortunately there is using SQL.

Let me start with the example file I am gong to use:

A R TESTFILER A F1 3A COLHDG('First' 'field') A F2 5A COLHDG('Second' 'field') A FDATE 7P 0 COLHDG('7 long' 'date')

That contains this data:

First Second 7 long field field date AAA FIRST 1,120,405 AAA SECND 1,080,914 BBB FIRST 1,120,305 BBB SECND 1,150,630

I build a Query to:

1. Join the file to itself on the field F2.
2. Convert the seven long date, FDATE, to the recognizable eight long.
3. Output to an outfile in my library, MYLIB.

I get:

First Second DATE1 First Second DATE2 field field field field AAA FIRST 20,120,405 BBB FIRST 20,120,305 AAA SECND 20,080,914 BBB SECND 20,150,630

Or I can retrieve the Column Headings for the outfile by using the SYSCOLUMNS View. If you are not familiar with using this View you ought to read Getting field definitions using SYSCOLUMNS.

SELECT SYSTEM_TABLE_NAME,SYSTEM_COLUMN_NAME, CAST(COLUMN_HEADING AS CHAR(50) CCSID(37) FROM QSYS2.SYSCOLUMNS WHERE SYSTEM_TABLE_SCHEMA = 'MYLIB' AND SYSTEM_TABLE_NAME = 'OUTFILE'

Which gives me:

SYSTEM_TABLE_NAME SYSTEM_COLUMN_NAME CAST function OUTFILE F1 First field OUTFILE F2 Second field OUTFILE DATE1 - OUTFILE F101 First field OUTFILE F201 Second field OUTFILE DATE2 -

This is not very user friendly.

As I mentioned at the start of this post I can change the Column Headings. I use the SQL LABEL ON COLUMN. In my example I want to change all of the headings. I could execute this SQL statement in embedded in RPG, a Query Management Query, use RUNSQLSTM, but I chose to do it in a CL program using the RUNSQL command. If you are not familiar with this command you really ought to learn how to use it, to learn more read Run SQL statements in your CL.

My RUNSQL command uses the "LABEL ON" with the "IS" to change the Column Headings:

RUNSQL SQL('LABEL ON COLUMN MYLIB/OUTFILE (+ F1 IS ''First field AAA'',+ F2 IS ''Second field AAA'',+ DATE1 IS ''Date AAA'',+ F101 IS ''First field BBB'',+ F201 IS ''Second field BBB'',+ DATE2 IS ''Date BBB'') ') + COMMIT(*NC)

When I repeat the SQL query over SYSCOLUMNS I see the change:

SYSTEM_TABLE_NAME SYSTEM_COLUMN_NAME CAST function OUTFILE F1 First field AAA OUTFILE F2 Second field AAA OUTFILE DATE1 Date AAA OUTFILE F101 First field BBB OUTFILE F201 Second field BBB OUTFILE DATE2 Date BBB

When I use the RUNQRY command to view the contents of the output file I see the new column headings:

First field Second field Date First field Second field Date AAA AAA AAA BBB BBB BBB AAA FIRST 20,120,405 BBB FIRST 20,120,305 AAA SECND 20,080,914 BBB SECND 20,150,630

It is that simple.

You can use the LABEL ON COLUMN to change the Column Headings for any file or table, not just outfiles produced by Query.

 

This article was written for IBM i 7.2, and should work for earlier releases too.