RE: sql union to control test results?

-----Message d'origine-----
De : midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] De la part de Birgitta Hauser
Envoyé : mercredi 7 juillet 2010 11:01
À : 'Midrange Systems Technical Discussion'
Objet : AW: sql union to control test results?

Did you compile your program with commit *NONE?
If not recreate it with option COMMIT *NONE or better add a
SET OPTION Commit = *NONE to your source code.
When adding a SET OPTION statement it must physically precede
all other SQL statements in your source.


Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the
stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek
Bok) "What is worse than training your staff and losing them?
Not training them and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von David FOXWELL
Gesendet: Wednesday, 07. July 2010 10:11
An: Midrange Systems Technical Discussion
Betreff: RE: sql union to control test results?


Dennis, all,

I've taken Dennis' program and used it to create a union
statement. At the end of the program, I have :

Exec SQL DROP TABLE QTEMP/T1;
Exec SQL EXECUTE IMMEDIATE : selectRun;

Debugging, selectRun shows :
CREATE TABLE QTEMP/T1 AS ( SELECT FLD1, FLD2 FROM MYTABLE
UNION SELECT FLD1,
FLD2 FROM MYTABLESAV ) WITH DATA which is what I want.

The instruction waits then I get SQLCOD -679

Job shows :
Modifications en instance de validation pour fichier T1 de QT047551.
Objet T1 de QTEMP type *FILE non créé car opération en instance.

Which means the file isn't created because an operation is
being validated.

What can this mean?

Thanks

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

Dennis Lovelady

Envoyé : lundi 5 juillet 2010 13:41 À : 'Midrange Systems Technical
Discussion'
Objet : 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.

--
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.


--
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.