Wednesday, August 10, 2016

Changing column headings in output file

using sql to change names of fields or columns in output file produced by query

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.

No comments:

Post a Comment

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.