Using SQR to create an SQR

[Denise White <dewhite@VICR.COM>]

Hi,

Several of you were talking about the possibility of using SQR to dynamically
create another SQR (under a somewhat unrelated thread title about .csv files).
As Franck pointed out, there are delivered SQR programs in the SQRBINW\SAMPLEW
directory that do this.  He mentioned export.sqr.  I have used this one, and I
have also used flatfile.sqr quite a bit.  There are others as well (Franck, is
there a list of them anywhere in the documentation?).  Export.sqr will write two
SQR programs: one to extract a delimited file from whatever table you specify,
and another to read and load that delimited file.  Flatfile.sqr will write an
SQR to extract a fixed-format file from the specified table.  Once you create
the SQR program, you can then modify it to include special selection criteria,
or whatever additional processing you need.  I have used flatfile.sqr
extensively when doing conversions or mass updates (such as merit increases), to
create files that can then be loaded into PeopleSoft using Import Manager.  I
have also used it to create files for archive purposes before purging the data.
I did modify the delivered version a bit, as I did not like the way it was
handling dates (they may have changed it since then, but it was truncating dates
to four characters, plus I wanted a different format for use with Import
Manager) or numeric fields, and because non-DBAs at my shop do not have access
to some of the tables it reads (there are shadow tables that we use, so I had to
change the table names).  These are really worthwhile programs to familiarize
yourself with.

Denise White
Sr. Programmer/Analyst
Vicor