AW: sql union to control test results?

["Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx>]

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.