Re: External Re: Count records in SQL

   Most probably the environment differs - like the transaction isolation
   level (between eSQL and STRSQL). 
   You can try different clauses to solve the problem. 
   Add "FOR READ ONLY" to your SQL statement
   Add "WITH NC" to your SQL statement
   Add "USE CURRENTLY COMMITTED" to your SQL statement
   Read about all 3 clauses here:
   -> [1]https://www.ibm.com/docs/en/i/7.6.0?topic=queries-select-statement
   HTH
   Daniel

     Am 19.02.2026 um 13:45 schrieb Jerry Forss <[email protected]>:

     

     Hi Daniel

      

     I am checking to see if there are any locks on the files using
     QSys2.Objecy_Lock_Info and getting a lock wait on a DtaQ in the target
     library, SqlSTT of 01687.

      

     Do you have any idea why using that would be locking?

     When I run it under strsql there are no issues.

      

         // Check If File Is Locked

         SqlSelect = 'SELECT Object_Schema, ' +

                           'Object_Name, '+

                           'Lock_Scope, ' +

                           'Job_Name ' +

                    'From QSYS2.Object_Lock_Info ' +

                    'Where OBJECT_SCHEMA = ' + SQ +
     %Trim(FileLibrary(FileIdx)) + SQ + ' And ' +

                    'OBJECT_NAME = ' + SQ + %Trim(PmKey3) + SQ;

      

         // Prepare Sql Statement

         Exec SQL Prepare LockCmd From :SqlSelect;

      

         // Declare Sql Cursor

         Exec SQL Declare LockCursor Cursor For LockCmd;

      

         // Run Sql Statement

         Exec SQL Open LockCursor;

      

         // Process Data Set

         DoU Done;

      

           // Get Employee Data

           Exec Sql Fetch Next From LockCursor Into : Object_Schema,

                                                    : Object_Name,

                                                    : Lock_Scope,

                                                    : Job_Name;

      

           //  If Eof Of Data or Unknown Error get out

           If   (SQLSTT <> SQLOK)

            And (SQLSTT <> SQLCmpError);

             Leave;

           EndIf;

                                                                 

      

      

     From: Daniel Gross <[email protected]>
     Sent: Friday, February 13, 2026 12:25 AM
     To: [email protected]
     Cc: Jerry Forss <[email protected]>
     Subject: External Re: Count records in SQL

      

     CAUTION: This email originated from outside of the organization. Do not
     click links or open attachments unless you recognize the sender and know
     the content is safe.

     Hi Jerry,

      

     of course, the easiest way to count records is a static SQL like:

      

     exec sql select count(*)

              into :nCountVar

              from ...

      

     But as I have seen, you have a dynamic SQL to tables in different
     schemas. And that's not possible in static SQL without creating aliases
     first. 

      

     But with dynamic SQL you can't have an INTO clause in your SELECT
     statement - so a possible solution would be:

      

     SqlSelect = 'select count(*) ' +
           'From ' + %Trim(PurgeXALib) + '/MBC6REP ' +
           'Where C6ACDT <= ' + %EditC(PurgeDateCYMD : 'X');

     SqlSelect = 'values ('+SqlSelect+') into ?';

     exec sql prepare stmSelectCount from :SqlSelect;

     exec sql execute stmSelectCount using :nCountVar;

      

     You have to wrap your SELECT in a VALUES/INTO statement with a dynamic
     parameter marker. Then prepare and execute it. 

      

     The solutions using GET DIAGNOSTICS after the OPEN doesn't help, as the
     database does not always return the exact number of rows in the cursor -
     it can either be estimated or (as this might be an ASENSITIVE or
     SENSITIVE cursor) it can change during the fetch loop.

      

     But if you want to read all the records anyway, you can do the
     following:

      

     SqlSelect = 'SELECT count(*) over(), ' +

                          'C6DcCd, '+

                          'C6CvNb, '+
                          'C6AcDt, ' +
                          'C6FnSt,' +
                          'C6B9Cd  ' +
                   'From ' + %Trim(PurgeXALib) + '/MBC6REP ' +
                   'Where C6ACDT <= ' + %EditC(PurgeDateCYMD : 'X');

     exec sql prepare stmSqlSelect from :SqlSelect;

     exec sql declare csrSqlSelect INSENSITIVE cursor for stmSqlSelect;

     exec sql open...

      

     exec sql fetch csrSqlSelect into :nCountVar, ...;

      

     This way you have to actual number of rows with every fetch in the
     variable. As the cursor is declared as INSENSITIVE, the rows won't
     change, even when new rows matching the criteria are inserted during
     runtime. 

      

         COUNT(*) OVER()

      

     is the simpliest form of an OLAP expression (aka window function). It's
     a very powerful feature of modern SQL and massively underrated and
     underutilized. 

      

     -> [2]https://www.ibm.com/docs/en/i/7.6.0?topic=expressions-olap-specifications

      

     In essence, you can calculate typical aggregates on every level of your
     select statement without using GROUP BY. Like having a grouped counter
     and row number over groups of rows or creating running totals, ranks and
     a lot more. 

      

     If you need the row count and the records, I would do it with OLAP. If
     you only need the count, the VALUES INTO solution is good.

      

     What is the purpose of the program? Or better what doesn't do with the
     fetched rows?

      

     If you need more help, feel free to contact me via the list or in
     private. 

      

     HTH

     Daniel

      

      

       Am 12.02.2026 um 21:16 schrieb Jerry Forss <[3][email protected]>:

       I have a SQL
        SqlSelect = 'SELECT C6DcCd, ' +
                            'C6CvNb, '+
                            'C6AcDt, ' +
                            'C6FnSt,' +
                            'C6B9Cd  ' +
                     'From ' + %Trim(PurgeXALib) + '/MBC6REP ' +
                     'Where C6ACDT <= ' + %EditC(PurgeDateCYMD : 'X');

       Instead of reading through the cursor, I want the number of records
       found.

       How do I do that?
       --
       This is the RPG programming on IBM i (RPG400-L) mailing list
       To post a message email: [4][email protected]
       To subscribe, unsubscribe, or change list options,
       visit: [5]https://lists.midrange.com/mailman/listinfo/rpg400-l
       or email: [6][email protected]
       Before posting, please take a moment to review the archives
       at [7]https://archive.midrange.com/rpg400-l.

       Please contact [8][email protected] for any subscription
       related questions.

     Subject to Change Notice:
     WalzCraft reserves the right to improve designs, and to change
     specifications without notice.
     Confidentiality Notice:
     This message and any attachments may contain confidential and privileged
     information that is protected by law. The information contained herein
     is transmitted for the sole use of the intended recipient(s) and should
     "only" pertain to "WalzCraft" company matters. If you are not the
     intended recipient or designated agent of the recipient of such
     information, you are hereby notified that any use, dissemination,
     copying or retention of this email or the information contained herein
     is strictly prohibited and may subject you to penalties under federal
     and/or state law. If you received this email in error, please notify the
     sender immediately and permanently delete this email. Thank You
     WalzCraft PO Box 1748 La Crosse, WI, 54602-1748
     [9]www.walzcraft.com Phone: 1-800-237-1326

References

   Visible links
   1. https://www.ibm.com/docs/en/i/7.6.0?topic=queries-select-statement
   2. https://www.ibm.com/docs/en/i/7.6.0?topic=expressions-olap-specifications
   3. mailto:[email protected]
   4. mailto:[email protected]
   5. https://lists.midrange.com/mailman/listinfo/rpg400-l
   6. mailto:[email protected]
   7. https://archive.midrange.com/rpg400-l
   8. mailto:[email protected]
   9. https://www.walzcraft.com/
-- 
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: [email protected]
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: [email protected]
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact [email protected] for any subscription related questions.