RE: Problem w/SQL

[Gina M Whitney <whitneyg@xxxxxxxxxx>]

Do you have dataDS defined more than once?  The names of host variables
used in SQL statements must be unique within the program, even if the host
variables are in different procedures.


> -----Original Message-----
> From: mwalter@xxxxxxxxxxxxxxx [mailto:mwalter@xxxxxxxxxxxxxxx]
> Sent: Wednesday, July 28, 2004 8:25 AM
> To: RPG programming on the AS400 / iSeries
> Subject: Problem w/SQL
>
>
>
> Could someone explain to me why this compiles?
>
>      DdsData           ds            83    occurs(10000) based(p_data)
>      d ds_cusno                       8  0 overlay(dsData:*next)
>      d ds_cusnn                      35    overlay(dsData:*next)
>      D ds_invdat                      8  0 overlay(dsData:*next)
>      D ds_actDte                      8  0 overlay(dsData:*next)
>      d ds_ordno                       6  0 overlay(dsData:*next)
>      D ds_ordlv                       2  0 overlay(dsData:*next)
>      D ds_invno                       6  0 overlay(dsData:*next)
>      D ds_msrshp                     10  3 overlay(dsData:*next)
>
>      PgetMTDDBI        b                   export
>      DgetMTDDBI        PI              *
>      D numRecs                        5i 0
>      D itnbr                         15    const
>      d fclyr                          4  0 const
>      d fclmo                          2  0 const
>
>      Dsize             S             10i 0
>      Dx                S              5i 0
>
>      C/exec sql
>      c+ SELECT DISTINCT COUNT(*) INTO :numRecs
>      c+  FROM Oedhst
>      c+  WHERE cuscl NOT IN ('ZC','ZH')
>      C+  AND house NOT IN ('8','Y')
>      C+  AND itnbr = :itnbr
>      C+  AND fclyr = :fclyr
>      C+  AND fclmo = :fclmo
>      c/end-exec
>
>      c                   if        numRecs = 0
>      C                   return    *null
>      c                   endif
>
>      C                   eval      size = %size(dsData) * numRecs
>      C                   alloc     size          p_data
>
>      c/exec sql
>      c+ DECLARE c CURSOR FOR
>      C+  SELECT b.cusno,b.cusnm,a.invdat,b.actdte,
>      c+         b.ordno,b.ordlv,b.invno,a.msrshp
>      c+  FROM Oedhst a JOIN Oehhst b
>      c+  ON a.ordno = b.ordno
>      c+  AND a.ordlv = b.ordlv
>      C+  WHERE a.fclyr = :fclyr
>      C+  AND   a.fclmo = :fclmo
>      c+  AND   a.itnbr = :itnbr
>      c+  AND   b.cuscl NOT IN ('ZH','ZC')
>      c+  AND   a.house NOT IN ('8','Y')
>      C+  ORDER BY  invdat,ordno,ordlv
>      c/end-exec
>
>      c/exec sql
>      c+ OPEN c
>      c/end-exec
>
>      c                   for       x = 1 to numRecs
>
>      c     x             occur     dsData
>
>      c/exec sql
>      c+ FETCH c  into :dsData
>      c/end-exec
>
>      c                   endfor
>
>      c/exec sql
>      c+ CLOSE c
>      c/end-exec
>
>      c                   return    p_data
>      P                 E
>
>
> And This doesn't:
>
>      DdataDS           DS            23    occurs(15)
>      D dsItnbr                       15    overlay(dataDS:*next)
>      D dsWkctr                        5    overlay(dataDS:*next)
>      D dsCpfSeq                       3  0 overlay(dataDS:*next) INZ
>
>      PbuildCursor      b
>      DbuildCursor      PI
>
>      C/exec sql
>      C+ DECLARE c1 CURSOR FOR
>      C+  SELECT DISTINCT itnbr,wkctr,cpfseq
>      C+   FROM Cpfpf
>      C/end-exec
>
>      C/exec sql
>      C+ OPEN c1
 >      C/end-exec
>
>      P                 E
>
>      PloadSubfile      B
>      DloadSubfile      PI
>
>      Dx                S             10i 0
>
>      C/exec sql
>      C+ FETCH FROM c1 FOR 15 ROWS INTO :datads
>      C/end-exec
>
>       ...
>
> Thanks,
>
> Mark
>
> Mark D. Walter
> Senior Programmer/Analyst
> CCX, Inc.
> mwalter@xxxxxxxxxx
> http://www.ccxinc.com