David, if I understand, I hope this will help.
This code, when completed will take any select statement (SELECT * FROM
MYTABLE in this case) and build a statement of the individual fields, so
that the statement will look like:
SELECT fld1, fld2, ... fld100
At completion, you could display this statement, write it to IFS or
whatever, but it will give you all the field names involved in the final
SELECT. Should make it easier to build the rest.
The program could be written such that the sqlStatement variable is passed
in, or built based upon a passed-in file name, or whatever makes the most
sense to you.
This is an extraction of my SQLtoXLS program, which is based loosely on
SQL2SYLK that can be found on the web. That program originated in France,
so the comments - should be quite readable to you. I don't remember anymore
how much French language was in the source, but there was certainly some.
D SelDA DS Qualified
// SELDA is the SQLDA data structure.
// In this case, we support up to 818 occurrences of
// variables. This limit is based upon the RPG limit
// of 65535 bytes for a data structure:
// 16 + (80 * 818) = 65456
//
// For complete description of SQLDA, have a look at:
//
http://publib.boulder.ibm.com/infocenter/iseries/v5r3..
// /index.jsp?topic=/sqlp/rbafysqldaformat.htm
D SQLDAID 8
D SQLDOUBLED 1 overlay(sqldaid:7)
D SQLDABC 10I 0
D SQLN 5u 0
D SQLD 5u 0
D SQLVar 80 DIM(818)
D SQLTYPE 5u 0 OVERLAY(SQLVar: 1)
D SQLLEN 5u 0 OVERLAY(SQLVar: 3)
D SQLRES 12 OVERLAY(SQLVar: 5)
D SQLDATA * OVERLAY(SQLVar:17)
D SQLIND * OVERLAY(SQLVar:33)
D SQLNAMELEN 5u 0 OVERLAY(SQLVar:49)
D SQLNAME 30 OVERLAY(SQLVar:51)
D sqlStatement 256 Varying
D selectOut 16384 Varying
sqlStatement = 'SELECT * FROM MYTABLE' ;
selectOut = 'SELECT ' ;
Exec SQL SET OPTION Naming = *SYS ;
Exec SQL DECLARE k1 CURSOR FOR k1prepa ;
Exec SQL PREPARE k1prepa FROM :sqlStatement ;
Exec SQL DESCRIBE k1prepa INTO :SelDA USING BOTH ;
For idxSQLVar = 1 to nbrCols ;
If idxSQLVar > 1 ;
selectOut += ', ' ;
EndIF ;
selectOut += %Trim(%Subst(SelDA.SQLVar.SQLNAME: 1
: SelDA.SQLVar.SQLNAMELEN)) ;
EndFOR;
// Write out the SELECT statement here (display, IFS, whatever)
Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
"If I were two-faced, would I be wearing this one?"
-- Abraham Lincoln
After running a test procedure, I found the results of the program
comparing the test results with the previous test very difficult to
read. The output file being compared contains various dates in
different formats. My modification changed nearly all these dates among
other things, so I ended up having to read a very large spool file as
all the test cases were affected.
I tried select fld1, fld2, etc from testfile union select fld1, fld2,
etc from savedtestfile, ignoring the different date fields. The test
cases having 2 lines in this select were those I wanted.
As the file in the select has a lot of zones, the sql wasn't easy to
code. I had to select all fields, then find those I didn't want, delete
them from the statement and copy the first half of the statement to the
second half.
Eg, select fld1, fld2, ....fld100, from testfile
delete fld3, fld74, etc from statement.
midrange.com
