RE: sql union to control test results?

You're right, David; the post had that error. It was intended as a concept,
but not necessarily presented that way. nbrCols just needs to be an integer
(meaning a whole decimal value) to meet FOR requirement.

If you pick up the tested/working/complete sltdrop.zip file from
http://www.lovelady.com/misc, you will find those problems corrected. It
likewise does not write out the SELECT statement, so it's only good in
STRDBG mode unless changed, but it provides the means of selecting all
fields except those listed.

I apologize for the confusion.

Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
"Thrusting my nose firmly between his teeth, I threw him heavily to the
ground on top of me."
-- Mark Twain

This looks interesting but I'm having difficulty compiling. I'm getting
7030 on the fields SQLNAMELEN and SQLNAME. I don't understand how you
can refer to these fields like this : SelDA.SQLVar.SQLNAME.

Also how is nbrCols defined? Thanks.

-----Message d'origine-----
De : midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] De la part de Dennis

Lovelady

Envoyé : vendredi 2 juillet 2010 10:46
À : 'Midrange Systems Technical Discussion'
Objet : RE: sql union to control test results?

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.

--
This is the Midrange Systems Technical Discussion
(MIDRANGE-L) mailing list To post a message email:
MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change
list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting,
please take a moment to review the archives at
http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.