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