Re: sql fetch into qualified data structure

["Brian Johnson" <brian.johnson.mn@xxxxxxxxx>]

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.