No announcement yet.

SLQRPGLE - Nested cursors over same file?

  • Filter
  • Time
  • Show
Clear All
new posts

  • SLQRPGLE - Nested cursors over same file?

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

    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;
          Exec SQL Close DetCurs;
       endif; // success_on_sql
    enddo; // xsqlstate2=success
    Exec SQL Close MstCurs;

    Thanks in advance.

  • #2
    Nevermind, there were unprintable characters hiding in the first record causing the xxdetl = 'DETAIL' comparison to fail. It's always the the silly stuff I miss. ´╗┐DETAIL = not very helpful.

    Sorry to have wasted cyberink.