RE: Embedded SQL Problem

[<Rick.Chevalier@xxxxxxxxxxxxxxx>]

With the code sample you have here, my suggestion would be to move the open of 
cursor B inside the cursor A loop and close cursor B prior to reading the next 
record with cursor A.

If you want to change the logic more drastically then Rob's join suggestion is 
a good one.

Rick

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of
THarteau@xxxxxxxxxxxxxxxxxx
Sent: Thursday, March 25, 2004 8:20 AM
To: RPG programming on the AS400 / iSeries
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.