Re: sql fetch into qualified data structure

Precompiler can only handle single-level qualification. Looks to me you
have two (t.pomtpfds2.field-name)...

[image: Start of change]The precompiler will recognize a host structure
defined using the LIKEDS keyword. However, the SQL syntax for a host
variable only allows using a single level of qualification in an SQL
statement. This means that if a data structure DS has a subfield S1 which is
defined like a data structure with a subfield S2, an SQL statement cannot
refer to S2 using the fully qualified host variable name of DS.S1.S2. If you
use S1.S2 as the host variable reference, the precompiler will recognize it
as DS.S1.S2. The following additional restrictions apply:

- The top level structure, DS, cannot be an array.
- S1.S2 must be unique. That is, there must be no other valid names in
the program ending with S1.S2, such as a structure S1 with a subfield
S1.S2, or a structure DS3 with a subfield DS3.S0.S1.S2.

[image: End of change]

On 4/4/07, Jim Horn <jimh@xxxxxxxxxxxxxx> wrote:

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



--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.