Re: External Re: Count records in SQL
Daniel Gross <[email protected]> Thu, 19 Feb 2026 15:40:31 +0100
| Newsgroups | gmane.comp.lang.as400.rpg |
|---|---|
| Message-ID | <[email protected]> |
And what was the solution?
Regards,
Daniel
Am 19.02.2026 um 14:42 schrieb Jerry Forss <[email protected]>:
I got it to work!
Thank you!
From: Jerry Forss
Sent: Thursday, February 19, 2026 7:17 AM
To: 'Daniel Gross' <[email protected]>
Cc: [email protected]
Subject: RE: External Re: Count records in SQL
I am also trying this but returns nothing.
SQLFile = File(FileIdx);
SQLLibrary = FileLibrary(FileIdx);
// Declare Sql Statement
Exec SQL Declare LockCursor Cursor For
SELECT Object_Schema, Object_Name, Lock_Scope, Job_Name
FROM QSYS2.OBJECT_LOCK_INFO
WHERE SYSTEM_OBJECT_SCHEMA = :SQLLibrary
And SYSTEM_OBJECT_NAME = :SQLFile;
Now, when I do a WRKOBJLCK it says there are no locks, but if I press F6
for member locks it shows many.
Am I missing something?
From: Daniel Gross <[1][email protected]>
Sent: Thursday, February 19, 2026 7:09 AM
To: Jerry Forss <[2][email protected]>
Cc: [3][email protected]
Subject: 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:
-> [4]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 <[5][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 <[6][email protected]>
Sent: Friday, February 13, 2026 12:25 AM
To: [7][email protected]
Cc: Jerry Forss <[8][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.
-> [9]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
<[10][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: [11][email protected]
To subscribe, unsubscribe, or change list options,
visit: [12]https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: [13][email protected]
Before posting, please take a moment to review the archives
at [14]https://archive.midrange.com/rpg400-l.
Please contact [15][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
[16]www.walzcraft.com Phone: 1-800-237-1326
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
[17]www.walzcraft.com Phone: 1-800-237-1326
References
Visible links
1. mailto:[email protected]
2. mailto:[email protected]
3. mailto:[email protected]
4. https://www.ibm.com/docs/en/i/7.6.0?topic=queries-select-statement
5. mailto:[email protected]
6. mailto:[email protected]
7. mailto:[email protected]
8. mailto:[email protected]
9. https://www.ibm.com/docs/en/i/7.6.0?topic=expressions-olap-specifications
10. mailto:[email protected]
11. mailto:[email protected]
12. https://lists.midrange.com/mailman/listinfo/rpg400-l
13. mailto:[email protected]
14. https://archive.midrange.com/rpg400-l
15. mailto:[email protected]
16. https://www.walzcraft.com/
17. 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.