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



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

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.