I have a file with a single record containing both header and detail information. I was attempting to use SQL to create a cursor to loop over the summary data and an inner cursor over the same file pulling the detail data. What I am seeing is the two cursors are sharing an access path, so the outer loop reads record 1, and the inner loop begins at record 2. I did a dspjob and I see the file open twice, but only one of the open files has had any I/O. I would have thought creating and opening two separate cursors would have created two separate access paths to the object.
Has anyone attempted to do something similar? Any idea how to successfully accomplish what I am attempting to do? Mr. Google hasn't been terribly helpful in trying to figure out what I am doing incorrectly.
Source code looks something like this....
Thanks in advance.
Has anyone attempted to do something similar? Any idea how to successfully accomplish what I am attempting to do? Mr. Google hasn't been terribly helpful in trying to figure out what I am doing incorrectly.
Source code looks something like this....
Code:
Exec SQL Declare MstCurs CURSOR FOR select distinct xxinvn, xxrc01, xxpono, xxvenc, xxinvd, xxdesc, xxpydd, xxinva, xxacrc, xxdorc, xxproc, xxprprf, xxprcz, xxreqk, xxrqid, '' from ccpayr where xxdetl = 'DETAIL' and xxpono <> ' ' and upper(xxli07) not in ('FREIGHT','TAX') for read only; Exec SQL Open MstCurs; DoW xSQLState2 = Success_On_Sql; Exec SQL Fetch MstCurs into :ImpMstDS; if xSQLState2 = Success_On_Sql; // Validate associated Detail line Records. Exec SQL Declare DetCurs CURSOR FOR select xxinvn, xxvenc, xxinvd, xxpono, xxliso, xxlids, xxliqt, xxliup, xxlitt, xxdorc, xxrc02, xxrc03, xxproc, xxprprf, xxprcz, 0, '', '' from ccpayr where xxinvn = :ImpMstDS.invn and xxdetl = 'DETAIL' and upper(xxli07) not in ('FREIGHT','TAX') for read only; Exec SQL Open DetCurs; DoW xSQLState2 = Success_On_Sql; Exec SQL Fetch DetCurs into :ImpDetDS; if xSQLState2 = Success_On_Sql; endif; enddo; Exec SQL Close DetCurs; endif; // success_on_sql enddo; // xsqlstate2=success Exec SQL Close MstCurs;
Thanks in advance.
Comment