× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. MIDRANGE-L
  3. July 2010

RE: sql union to control test results?

Sounds like an interesting idea. I hope it serves you well.

Looks like you already got the advice you needed regarding STRDBG eval, but
basically:

eval myvar:c 16384

(or similar) is what you were seeking.

Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
"Don't knock the weather; nine tenths of the people couldn't start a
conversation if it didn't change once in a while."
-- Kin Hubbard



I had to test "that" file again so I quickly downloaded and compiled.
But in debug mode, I seem to remember that there is a limit to how many
characters are shown in a variable, so I can't see all my fields.

I think I should be able to adapt the program quite easily so it
actually runs the select on the file with UNION on the saved version of
the file and writes out the differences. That should make a nice tool
for a test library.

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



As an Amazon Associate we earn from qualifying purchases.

This thread ...
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.