RE: Problem w/SQL

[<Rick.Chevalier@xxxxxxxxxxxxxxx>]

Mark,

I do this kind of thing all the time without a problem so I know it can be 
done.  Convincing the SQL pre-compiler it can be done is sometimes frustrating.

I have three suggestions:

1)  Remove the Inz from sub field dsCpfSeq.  This isn't needed as the fields 
will be set when the Fetch is run.

2)  Remove the Overlay keywords.  The pre-compiler can be pretty picky and I 
wouldn't be surprised if this is causing it to hiccup.  

3)  Confirm that the field sizes and types match between the DS and the fields 
returned in the Fetch.

I came into this thread late so if you have already tried these suggestions I'm 
sorry to duplicate.

If these don't work post the current code you are working with and maybe 
something else will jump out.

HTH,

Rick

-----Original Message-----
From: rpg400-l-bounces+rick.chevalier=americredit.com@xxxxxxxxxxxx
[mailto:rpg400-l-bounces+rick.chevalier=americredit.com@xxxxxxxxxxxx]On
Behalf Of mwalter@xxxxxxxxxxxxxxx
Sent: Wednesday, July 28, 2004 7: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


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