Tweet
I'm trying to figure out what I am doing wrong here... In the code below, I am getting some order information within a DOU loop. Within that loop, I'm then using that information to read another file to get the "license plate" field. After that, I insert the record on a new file. I don't know how many records there may be. So, if the license plate file gets SQL = 100, I'm assuming that I've finished that order-suffix-line-count and go back to my outer loop to get the next record.
The first loop through works. It finds the information on file 2 (LICPLTEPF) and ends the loop. On the next attempt, I'm getting SQLCOD 100 - although I know that the record is on the file. I've run it online with STRSQL and it works.
To recap: I need to loop through file 1 to get the ordered line item, loop through file 2 to get the license plate number (if ordered qty = 2, there will be 2 license plate records), and then update file 3.
I'm sure it's either a stupid mistake or there is an easier way to do this! If anyone has any ideas, please let me know. Since it's been raining daily here for over a week, I think my brain is waterlogged!
Thank you.
The first loop through works. It finds the information on file 2 (LICPLTEPF) and ends the loop. On the next attempt, I'm getting SQLCOD 100 - although I know that the record is on the file. I've run it online with STRSQL and it works.
To recap: I need to loop through file 1 to get the ordered line item, loop through file 2 to get the license plate number (if ordered qty = 2, there will be 2 license plate records), and then update file 3.
I'm sure it's either a stupid mistake or there is an easier way to do this! If anyone has any ideas, please let me know. Since it's been raining daily here for over a week, I think my brain is waterlogged!
Thank you.
PHP Code:
//===============================================================
BegSr $100_GetHdrDtls; // Get Order Hdr/Dtls
//===============================================================
// Loop thru Order Hdr/Dtls by Input Parms
// Declare Cursor C1 - Find OrHeadrPF/ORDTAILPF Records by ASL
Exec SQL
Declare C1HDRDTLS cursor for
Select A.*, B.* , C.*
from ORHeadrPF A Inner Join ORDtailPF B
On A.AEA4CD = B.AGA4CD and /* Hdr PLT ID */
A.AEA5CD = B.AGA5CD and /* Hdr ACK NBR */
A.AEA2NB = B.AGA2NB /* Hdr ACK SUF */
JOIN SCFGSCHPF C
On
A.AEA4CD = C.ALBVCD and
A.AEA5CD = C.ALDWCD and
A.AEA2NB = C.ALB3NB and
B.AGBCNB = C.ALB4NB
where A.AEA4CD = :CurPlt and /* Parm Plant */
A.AEA3NB = :PLoad and /* Parm Load */
A.AEA6CD = :PStop and /* Parm Stop */
A.AEANDT = :PLoadDte and /* load date */
C.ALDTCD = :PAsyLn and
(AEA1ST = 'R' or AEA1ST = 'P') /* hdr Status */
Order by A.AEA4CD, /* Hdr PLT ID */
C.ALDTCD, /* ASY LINE */
A.AEA3NB, /*OFS LOAD*/
A.AEA6CD DESC, /*STOP */
SUBSTR(A.AELKCD,1,2); /*SEQUENCE*/
Exec SQL
Open C1HDRDTLS;
OrdEOF = 'N';
// Process the cursor
DoU OrdEOF = 'Y';
CLEAR ORHEADRDS;
CLEAR ORDTAILDS;
CLEAR SCFGSCHDS;
EXEC SQL
FETCH next from C1HDRDTLS into :ORHEADRds, :ORDTAILds, :SCFGSCHds;
// Test for EOF
If SqlCod <> 0;
OrdEOF = 'Y';
ITER;
EndIf;
k1_ACK = AEA5CD;
k1_Suffix = AEA2NB;
// Determine Difficulty Code
// ....some calculations here
// get all records for order/suffix/line
//from LP file.... from 1 to 9999
EXEC SQL
DECLARE C2LICPLTE Cursor for
Select * from LICPLTEPF
Where LPLPLT = :CurPlt and
LPLACK = :AEA5CD and
LPLSUF = :AEA2NB and
LPLLNE = :AGBCNB and
LPLASYLN = :ALDTCD and
LPLLPNBR = :LicPltIdx;
EXEC SQL
OPEN C2LICPLTE;
LicPltIdx = 1;
DOU LicPltIdx > 9998;
Exsr $220_Insert; // Insert Records
ENDDO;
EXEC SQL
CLOSE C2LICPLTE;
EndDo;
EXEC SQL
Close C1HDRDTLS;
EndSr;
BegSr $220_Insert; // Insert Records
clear LICPLTEDS;
EXEC SQL
FETCH next from C2LICPLTE into :LICPLTEds ;
If SqlCod <> 0;
LicPltIdx = 9999;
LeaveSR;
EndIf;
// Make Records Changes
Comment