Re: Embedded SQL Problem

[rob@xxxxxxxxx]

Two methods come to mind.

1)  Close and reopen B.  When the customer number changes, (from A), then 
you need to reopen B to use the new customer number.

2)  JOIN
(Our version of BPCS doesn't have CMFF01 or ILCLAS. Therefore I left out 
ILCLAS and substituted CZIP for CMFF01.)
SELECT A.CCUST, A.CZIP, 
       B.ILINVN, B.ILLINE, B.ILPROD, B.ILCUST, B.ILQTY, B.ILDATE,
       B.ILNET,           B.ILLTYP 
FROM RCM A JOIN SIL B ON A.CCUST=B.ILCUST 
WHERE A.CZIP<>' ' AND B.ILDATE BETWEEN :DteBeg AND :DteEnd
ORDER BY A.CZIP, A.CCUST, B.ILDATE 

Rob Berendt
-- 
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





THarteau@xxxxxxxxxxxxxxxxxx 
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
03/25/2004 09:20 AM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>


To
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>
cc

Subject
Embedded SQL Problem







Hi,
      I have a program that reads selected customers (RCM) then reads all
the invoice lines (SIL) for a time period for each customer. I am picking
up the correct customers, but not getting any invoice lines. I have 
checked
and the first couple of customers had no invoices in Jan, but others do. 
Do
I need to reset the invoice file to the beginning for each customer? I 
have
never done that before, but I keep getting SQL code 100 (end of file). 
Here
is my code:

0093.00 C******************************
0094.00 C/EXEC SQL DECLARE A CURSOR FOR
0095.00 C+ SELECT CCUST,CMFF01 FROM RCM WHERE CMFF01 <> :Blk16
0096.00 C+ ORDER BY CMFF01,CCUST
0097.00 C/END-EXEC
0098.00 C******************************
0099.00 C/EXEC SQL
0100.00 C+    OPEN A
0101.00 C/END-EXEC
0102.00 C******************************
0109.00 C/EXEC SQL DECLARE B CURSOR FOR
0110.00 C+ SELECT
0111.00 C+ ILINVN,ILLINE,ILPROD,ILCUST,ILQTY,ILDATE,ILNET,ILCLAS,ILLTYP
0112.00 C+  FROM SIL WHERE ILCUST = :CUSTNO and ILDATE >= :DteBeg and
0113.00 C+  ILDATE <= :DteEnd ORDER BY ILDATE
0114.00 C/END-EXEC
0115.00 C******************************
0116.00 C/EXEC SQL
0117.00 C+    OPEN B
0118.00 C/END-EXEC
0119.00 C******************************
0120.00 C*
0121.00 C                   EVAL      MorRCM = 'YES'
0122.00 C*
0123.00 C                   DOU       MorRCM = 'NO '
0124.00 C*
0125.00 C/EXEC SQL
0126.00 C+ FETCH NEXT FROM A INTO :RCMRec
0127.00 C/END-EXEC
0128.00 C*
0129.00 C                   IF        SQLCOD <> 0
0130.00 C                   EVAL      MorRCM = 'NO '
0131.00 C                   ELSE
0132.00 C                   EVAL      CustNo = CCUST
0134.00 C                   EVAL      CusId = CMFF01
0135.00 C                   EVAL      MorSIL = 'YES'
0136.00 C*
0137.00 C                   DOU       MorSIL = 'NO '
0138.00 C*
0139.00 C/EXEC SQL
0140.00 C+ FETCH NEXT FROM B INTO :SILMPG
0141.00 C/END-EXEC
0142.00 C*
0143.00 C                   IF        SQLCOD <> 0

More code.....

This last SQLCOD = 100

Any ideas?

<===================================================>

Terri Harteau
Felker Brothers Corporation
Marshfield, WI
****************
"There's no point in being grown up if you can't be childish sometimes."
- Dr. Who
****************






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