|
Nope. Thanks, Mark Mark D. Walter Senior Programmer/Analyst CCX, Inc. mwalter@xxxxxxxxxx http://www.ccxinc.com Gina M Whitney <whitneyg@xxxxxxxx To: rpg400-l@xxxxxxxxxxxx om> cc: Sent by: Subject: RE: Problem w/SQL rpg400-l-bounces@m idrange.com 07/28/2004 09:34 AM Please respond to RPG programming on the AS400 / iSeries 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 -- 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.
As an Amazon Associate we earn from qualifying purchases.
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.
midrange.com
