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



Thanks Brian and Birgitta.

Birgitta -

The separate data structures (even if not qualified worked great).

You are right on the sql select being dynamic. This was a snippet of the
actual sql built by the program into a string. We currently use them only
for intermittent queries which are not run very much or for queries where
there are so many possible select parameters that the static sql is beyond
me. In the future, I hope to do a better job with case statements, passed
variables and static sql.

The one thing making this still not work is the null returns on missing left
outer joins. Currently without the indicator variables, the sql fetch
returns an error if any of the fields are null. Any possibility there is
some setting to return default values (blanks or zeros's) instead of nulls?
This used to work when selecting from dds join logical files. (ok, I know,
the old days are gone.) Otherwise is there some standard way to create an
indicator array and add it to the fetch command without specifying all the
fields separately? We have a lot of this kind of thing to convert from some
old techniques to some newer ones (lightly used stuff and we have lots of
cycles) and would like some generic (not necessarily optimized) method to
get things done.

----------------------------------------------------------------------
Could the indicator array somehow be easily added to this?

D pomtpftemp E DS EXTNAME(pomtpf) prefix(z)
D pomhpftemp E DS EXTNAME(pomhpf) prefix(z)
D pdlcpftemp E DS EXTNAME(pdlcpf) prefix(z)
C/EXEC SQL
C+ prepare p1 from :string
C/end-exec
C/EXEC SQL
C+ declare c1 cursor for p1
C/end-exec
C/EXEC SQL
C+ open c1
C/end-exec
C/EXEC SQL
C+ fetch c1 into :pomtpftemp, :pomhpftemp, :pdlcpftemp
C/end-exec
------------------------------------------------------------------------
Thanks again

Jim



message: 7
date: Thu, 5 Apr 2007 07:29:20 +0200
from: "BirgittaHauser" <Hauser@xxxxxxxxxxxxxxx>
subject: AW: sql fetch into qualified data structure

Hi,

Instead of defining 3 data structures, reference them and put them into a
single data structure, I'd fetch the result directly into the three
qualified datastructures.
Because of the left outer joins you should also define and use indicator
arrays to check NULL values.

D* DATA STRUCTURE FOR SQL field definitions
D pomtpfds2 E DS EXTNAME(pomtpf) qualified
D pomhpfds2 E DS EXTNAME(pomhpf) qualified
D pdlcpfds2 E DS EXTNAME(pdlcpf) qualified

D STRING S 512
/FREE

STRING = 'SELECT * FROM POMTPF LEFT OUTER JOIN POMHPF ON PONUM = PMNUM +
LEFT OUTER JOIN PDLC ON POPDL = IAPDL AND +
DEC(SUBSTR(DIGITS(PONUM),1,2)) = IALOC +
WHERE PONUM = 1 +

ORDER BY POVEND, PONUM, POLINE';
/END-FREE

C/EXEC SQL
C+ prepare p1 from :string
C/end-exec
C*
C/EXEC SQL
C+ declare c1 cursor for p1
C/end-exec
C*
C/EXEC SQL
C+ open c1
C/end-exec
C*-------------------------------------------------------------------
C*
c*---------------------------------------------------------------
C* example of sql fetch
C/EXEC SQL
C+ fetch c1 into :pomtpfds2, :pomhpfds2, :pdlcpfds2

C/end-exec

C seton LR

Just an other question:
Why are you using dynamic SQL instead of static?

As it looks like the SQL-statement is already completely known at compile
time.
Static SQL performs better, because syntax checking is alread done at
compile time.
Also an access plan is built and stored in the programm object at compile
time. At runtime the access plan will only be validated and if necessary
actualized. For all subsequent calls this access plan can be used for
building the ODP (open data path).

With dynamic SQL an access plan cannot be stored in the program object. If
there is no access plan available that can be used (i.e. in the SQE plan
cache, in the system wide cache, in job's memory) the access plan must be
created by scratch at run time.

If the prepare statement will be executed each time the program runs, a
complete optimization including opening the data path (open data path),
which is the most expensive process when executing a SQL statement, must be
performed. The ODP never can be reused.

All these points will decrease performance considerably.

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: rpg400-l-bounces+hauser=sss-software.de@xxxxxxxxxxxx
[mailto:rpg400-l-bounces+hauser=sss-software.de@xxxxxxxxxxxx] Im Auftrag von
Jim Horn
Gesendet: Wednesday, April 04, 2007 23:44
An: rpg400-l@xxxxxxxxxxxx
Betreff: sql fetch into qualified data structure


I am trying to build a generic way to do fetches on sql joins without
defining all the variables over again. Want to do a -fetch into- a
qualified data structure which has all the fields in the join. (the real
program uses most of them). Can't build a join logical file because the
fields in the files don't match.

Program below

D

D* DATA STRUCTURE FOR SQL field definitions

D pomtpfds E DS EXTNAME(pomtpf)

D pomhpfds E DS EXTNAME(pomhpf)

D pdlcpfds E DS EXTNAME(pdlcpf)

D

D T ds QUALIFIED

D pomtpfds2 LIKEDS(pomtpfds)

D pomhpfds2 LIKEDS(pomhpfds)

D pdlcpfds2 LIKEDS(pdlcpfds)

D

D STRING S 512

C

/FREE

STRING = 'SELECT * FROM POMTPF LEFT OUTER JOIN POMHPF ON PONUM = PMNUM +

LEFT OUTER JOIN PDLC ON POPDL = IAPDL AND +

DEC(SUBSTR(DIGITS(PONUM),1,2)) = IALOC +

WHERE PONUM = 1 ORDER BY POVEND, PONUM, POLINE';

/END-FREE
C
C*
C/EXEC SQL
C+ prepare p1 from :string
C/end-exec
C*
C/EXEC SQL
C+ declare c1 cursor for p1
C/end-exec
C*
C/EXEC SQL
C+ open c1
C/end-exec
C*-------------------------------------------------------------------
C*
c*---------------------------------------------------------------
C* example of sql fetch
C/EXEC SQL
C+ fetch c1 into :T
C/end-exec

C*

C seton LR


Getting this

*...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+.
...8.
5722ST1 V5R4M0 060210 Create SQL ILE RPG Object
ICPODRGTST
Record *...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+...
7 ..
39 C+ fetch c1 into :T

5722ST1 V5R4M0 060210 Create SQL ILE RPG Object
ICPODRGTST
DIAGNOSTIC MESSAGES

MSG ID SEV RECORD TEXT

SQL0312 30 39 Position 25 Variable T not defined or not usable.

Message Summary

Total Info Warning Error Severe Terminal

1 0 0 0 1 0

30 level severity errors found in source

42 Source records processed

* * * * * E N D O F L I S T I N G * * * *
*

The top part (below) compiles fine as an rpgle.
D

D* DATA STRUCTURE FOR SQL field definitions

D pomtpfds E DS EXTNAME(pomtpf)

D pomhpfds E DS EXTNAME(pomhpf)

D pdlcpfds E DS EXTNAME(pdlcpf)

D

D T ds QUALIFIED

D pomtpfds2 LIKEDS(pomtpfds)

D pomhpfds2 LIKEDS(pomhpfds)

D pdlcpfds2 LIKEDS(pdlcpfds)



Any ideas?


Thanks

Jim




As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

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.