RE: External RE: Count records in SQL -- RPG400-L

Jerry,
Having worked (and still working) with Infor XA, I am sure you are aware of all the files that are not commonly known but will need to get purged/deleted. Otherwise, you may end up with orphaned records with no direct links to the MBC6REP record(s). There are potentially too many to list...

Good luck...
Eric A. Wolf
Sr. Program Developer
2980 N. San Fernando Blvd.<https://www.google.com/maps/place/2980+N+San+Fernando+Blvd,+Burbank,+CA+91504/@34.2016192,-118.3414462,17z/data=!3m1!4b1!4m2!3m1!1s0x80c29506b13e342b:0x8e7c0e2b16b32dff>
Burbank, CA 91504-2566<https://www.google.com/maps/place/2980+N+San+Fernando+Blvd,+Burbank,+CA+91504/@34.2016192,-118.3414462,17z/data=!3m1!4b1!4m2!3m1!1s0x80c29506b13e342b:0x8e7c0e2b16b32dff>
[cid:image001.png@01DCA025.4D1777D0] (818) 260-2936
[cid:image002.jpg@01DCA025.4D1777D0] (818) 669-0636
[cid:image003.png@01DCA025.4D1777D0] ewolf@xxxxxxxxxxxxx<mailto:ewolf@xxxxxxxxxxxxx>
[cid:image004.png@01DCA025.4D1777D0]<https://seniorssp.com/>
A division of Senior Operations LLC

From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Don Brown via RPG400-L
Sent: Tuesday, February 17, 2026 3:37 PM
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Cc: Don Brown <dbrown@xxxxxxxxxxxxxxxx>
Subject: RE: External RE: Count records in SQL

Hi Jerry, I am surprised you can just delete records from the files to be purged. We did spend quite some time building purge processes to ensure we did not break any referential integrity as part of removing records and this process needs
NkdkJdXPPEBannerStart
External Sender - From: (Don Brown via RPG400-L <rpg400-l@xxxxxxxxxxxxxxxxxx>)

This message came from outside your organization.

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.

NkdkJdXPPEBannerEnd

Hi Jerry,

I am surprised you can just delete records from the files to be purged.

We did spend quite some time building purge processes to ensure we did not

break any referential integrity as part of removing records and this

process needs some regular checking and updating.

We have some clients with over 25 years of history and now trying to

convince them to actually purge some of that data is the difficult bit ...

if you know what I mean!

Cheers

Don

Don Brown

Senior Consultant

[1]OneTeam IT Pty Ltd

P: 1300 088 400

-----Original Message-----

From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx<mailto:rpg400-l-bounces@xxxxxxxxxxxxxxxxxx>> On Behalf Of Jerry

Forss

Sent: Tuesday, 17 February 2026 11:57 PM

To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx<mailto:rpg400-l@xxxxxxxxxxxxxxxxxx>>

Subject: RE: External RE: Count records in SQL

Thank you ALL for your great explanations!

What I am doing is setting up a purge process for our main packages.

This has NEVER been done as they always wanted to have all history for

ever and ever.

This has caused several issues, not to say the least Invoice Numbers being

reused some 15 years later.

1 - Get Purge through date (I have convinced them at 8 years)

2 - List all files to be purged and use SQL to determine number of records

that are going to be purged using SQL.

This will also verify that there are no locks on the file as well.

3 - Display list file files/records as a verification.

4 - If No locks found and continue is selected.

Create Purge Library

Loop until done

Allocate file

CrtDupObj of each File in Purge Library

CPYF records from Live File to Duplicate file

Delete records from Live File using SQL

Reorg file

UnAllocate file

We have plenty of space on our box so no need to remove them from the

system and want them somewhere incase They MIGHT be needed in an inquiry.

Again, thank you all!

-----Original Message-----

From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx<mailto:rpg400-l-bounces@xxxxxxxxxxxxxxxxxx>> On Behalf Of Birgitta

Hauser

Sent: Thursday, February 12, 2026 10:51 PM

To: 'RPG programming on IBM i' <rpg400-l@xxxxxxxxxxxxxxxxxx<mailto:rpg400-l@xxxxxxxxxxxxxxxxxx>>

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.

GET DIAGNOSTICS ... will only return information AFTER an SQL Statement is

run.

ROW_COUNT: (Except from the SQL Reference) Identifies the number of rows

associated with the previous SQL statement that was executed. If the

previous SQL statement is a DELETE, INSERT, REFRESH, or UPDATE statement,

ROW_COUNT identifies the number of rows deleted, inserted, or updated by

that statement, excluding rows affected by either triggers or referential

integrity constraints. If the previous SQL statement is a MERGE statement,

ROW_COUNT identifies the total number of rows deleted, inserted, and

updated by that statement, excluding rows affected by either triggers or

referential integrity constraints. If the previous SQL statement is a

multiple-row-fetch, ROW_COUNT identifies the number of rows fetched.

Otherwise, the value zero is returned.

May be DB2_NUMBER_ROWS would be the better option: (Except from the SQL

Reference)

If the previous SQL statement was an OPEN or a FETCH which caused the size

of the result table to be known, returns the number of rows in the result

table. For SENSITIVE cursors, this value can be thought of as an

approximation since rows inserted and deleted will affect the next

retrieval of this value. Otherwise, the value zero is returned.

Mit freundlichen Grï¿½ï¿½en / Best regards

Birgitta Hauser

Modernization - Education - Consulting on IBM i Database and Software

Architect IBM Champion since 2020

"Shoot for the moon, even if you miss, you'll land among the stars." (Les

Brown)

"If you think education is expensive, try ignorance." (Derek Bok) "What is

worse than training your staff and losing them? Not training them and

keeping them!"

"Train people well enough so they can leave, treat them well enough so

they don't want to. " (Richard Branson) "Learning is experience ...

everything else is only information!" (Albert

Einstein)

-----Original Message-----

From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx<mailto:rpg400-l-bounces@xxxxxxxxxxxxxxxxxx>> On Behalf Of Jimmy

Sansi

Sent: Thursday, 12 February 2026 21:44

To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx<mailto:rpg400-l@xxxxxxxxxxxxxxxxxx>>

Subject: Re: Count records in SQL

What about ...

exec sql GET DIAGNOSTICS :Rows = ROW_COUNT;

[2]https://urldefense.proofpoint.com/v2/url?u=https-3A__www.ibm.com_docs_en_i_7.5.0-3Ftopic-3Dstatements-2Dget-2Ddiagnostics&d=DwIFAw&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=PCeNSf9fhcAUxJzoAZa9Db8EQ_28MTQACuQxevMqYzc&m=-Lo4vkMFad4Y7lYQ3lwhRG7BtzGPbBimhdaVkHyUXdSc4cI_cwzVKJWo6EeQGa3T&s=U8cgVc8kAAP760bm4OJRo8D6RYNBYDsUlV-Yg34P8gQ&e=

On 2026-02-12 12:15, Jerry Forss wrote:

> 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: RPG400-L@xxxxxxxxxxxxxxxxxx<mailto:RPG400-L@xxxxxxxxxxxxxxxxxx> To subscribe, unsubscribe, or

change list options,

visit: [3]https://urldefense.proofpoint.com/v2/url?u=https-3A__lists.midrange.com_mailman_listinfo_rpg400-2Dl&d=DwIFAw&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=PCeNSf9fhcAUxJzoAZa9Db8EQ_28MTQACuQxevMqYzc&m=-Lo4vkMFad4Y7lYQ3lwhRG7BtzGPbBimhdaVkHyUXdSc4cI_cwzVKJWo6EeQGa3T&s=f4jusuGMobkL4UExY_gUM2padFMs1MqePaJH_7N9Ots&e=

or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx<mailto:RPG400-L-request@xxxxxxxxxxxxxxxxxx>

Before posting, please take a moment to review the archives at

[4]https://urldefense.proofpoint.com/v2/url?u=https-3A__archive.midrange.com_rpg400-2Dl&d=DwIFAw&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=PCeNSf9fhcAUxJzoAZa9Db8EQ_28MTQACuQxevMqYzc&m=-Lo4vkMFad4Y7lYQ3lwhRG7BtzGPbBimhdaVkHyUXdSc4cI_cwzVKJWo6EeQGa3T&s=i_6H7b9sONvs61ZAjOsR-8FDEZma3cSacn4e0xhE1Sw&e=.

Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx> for any subscription related

questions.

--

This is the RPG programming on IBM i (RPG400-L) mailing list To post a

message email: RPG400-L@xxxxxxxxxxxxxxxxxx<mailto:RPG400-L@xxxxxxxxxxxxxxxxxx> To subscribe, unsubscribe, or

change list options,

visit: [5]https://urldefense.proofpoint.com/v2/url?u=https-3A__lists.midrange.com_mailman_listinfo_rpg400-2Dl&d=DwIFAw&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=PCeNSf9fhcAUxJzoAZa9Db8EQ_28MTQACuQxevMqYzc&m=-Lo4vkMFad4Y7lYQ3lwhRG7BtzGPbBimhdaVkHyUXdSc4cI_cwzVKJWo6EeQGa3T&s=f4jusuGMobkL4UExY_gUM2padFMs1MqePaJH_7N9Ots&e=

or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx<mailto:RPG400-L-request@xxxxxxxxxxxxxxxxxx>

Before posting, please take a moment to review the archives at

[6]https://urldefense.proofpoint.com/v2/url?u=https-3A__archive.midrange.com_rpg400-2Dl&d=DwIFAw&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=PCeNSf9fhcAUxJzoAZa9Db8EQ_28MTQACuQxevMqYzc&m=-Lo4vkMFad4Y7lYQ3lwhRG7BtzGPbBimhdaVkHyUXdSc4cI_cwzVKJWo6EeQGa3T&s=i_6H7b9sONvs61ZAjOsR-8FDEZma3cSacn4e0xhE1Sw&e=.

Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx> for any subscription related

questions.

--

This is the RPG programming on IBM i (RPG400-L) mailing list To post a

message email: RPG400-L@xxxxxxxxxxxxxxxxxx<mailto:RPG400-L@xxxxxxxxxxxxxxxxxx> To subscribe, unsubscribe, or

change list options,

visit: [7]https://urldefense.proofpoint.com/v2/url?u=https-3A__lists.midrange.com_mailman_listinfo_rpg400-2Dl&d=DwIFAw&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=PCeNSf9fhcAUxJzoAZa9Db8EQ_28MTQACuQxevMqYzc&m=-Lo4vkMFad4Y7lYQ3lwhRG7BtzGPbBimhdaVkHyUXdSc4cI_cwzVKJWo6EeQGa3T&s=f4jusuGMobkL4UExY_gUM2padFMs1MqePaJH_7N9Ots&e=

or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx<mailto:RPG400-L-request@xxxxxxxxxxxxxxxxxx>

Before posting, please take a moment to review the archives at

[8]https://urldefense.proofpoint.com/v2/url?u=https-3A__archive.midrange.com_rpg400-2Dl&d=DwIFAw&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=PCeNSf9fhcAUxJzoAZa9Db8EQ_28MTQACuQxevMqYzc&m=-Lo4vkMFad4Y7lYQ3lwhRG7BtzGPbBimhdaVkHyUXdSc4cI_cwzVKJWo6EeQGa3T&s=i_6H7b9sONvs61ZAjOsR-8FDEZma3cSacn4e0xhE1Sw&e=.

Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx> for any subscription related

questions.

--

Message protected by MailGuard: e-mail anti-virus, anti-spam and content

filtering.

[9]https://urldefense.proofpoint.com/v2/url?u=https-3A__www.mailguard.com.au&d=DwIFAw&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=PCeNSf9fhcAUxJzoAZa9Db8EQ_28MTQACuQxevMqYzc&m=-Lo4vkMFad4Y7lYQ3lwhRG7BtzGPbBimhdaVkHyUXdSc4cI_cwzVKJWo6EeQGa3T&s=_vcsktfH-1fiH5lEoERNe9ZZbYVGsO1bzzeeU3bnaYU&e=

References

Visible links

1. https://urldefense.proofpoint.com/v2/url?u=https-3A__www.oneteamit.com.au_&d=DwIFAw&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=PCeNSf9fhcAUxJzoAZa9Db8EQ_28MTQACuQxevMqYzc&m=-Lo4vkMFad4Y7lYQ3lwhRG7BtzGPbBimhdaVkHyUXdSc4cI_cwzVKJWo6EeQGa3T&s=tIWxKwc_Nuxy3ZLlAv5G7kiabmtNy0I1kUaIoa7scjQ&e=

2. https://urldefense.proofpoint.com/v2/url?u=https-3A__www.ibm.com_docs_en_i_7.5.0-3Ftopic-3Dstatements-2Dget-2Ddiagnostics&d=DwIFAw&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=PCeNSf9fhcAUxJzoAZa9Db8EQ_28MTQACuQxevMqYzc&m=-Lo4vkMFad4Y7lYQ3lwhRG7BtzGPbBimhdaVkHyUXdSc4cI_cwzVKJWo6EeQGa3T&s=U8cgVc8kAAP760bm4OJRo8D6RYNBYDsUlV-Yg34P8gQ&e=

3. https://urldefense.proofpoint.com/v2/url?u=https-3A__lists.midrange.com_mailman_listinfo_rpg400-2Dl&d=DwIFAw&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=PCeNSf9fhcAUxJzoAZa9Db8EQ_28MTQACuQxevMqYzc&m=-Lo4vkMFad4Y7lYQ3lwhRG7BtzGPbBimhdaVkHyUXdSc4cI_cwzVKJWo6EeQGa3T&s=f4jusuGMobkL4UExY_gUM2padFMs1MqePaJH_7N9Ots&e=

4. https://urldefense.proofpoint.com/v2/url?u=https-3A__archive.midrange.com_rpg400-2Dl&d=DwIFAw&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=PCeNSf9fhcAUxJzoAZa9Db8EQ_28MTQACuQxevMqYzc&m=-Lo4vkMFad4Y7lYQ3lwhRG7BtzGPbBimhdaVkHyUXdSc4cI_cwzVKJWo6EeQGa3T&s=i_6H7b9sONvs61ZAjOsR-8FDEZma3cSacn4e0xhE1Sw&e=.

5. https://urldefense.proofpoint.com/v2/url?u=https-3A__lists.midrange.com_mailman_listinfo_rpg400-2Dl&d=DwIFAw&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=PCeNSf9fhcAUxJzoAZa9Db8EQ_28MTQACuQxevMqYzc&m=-Lo4vkMFad4Y7lYQ3lwhRG7BtzGPbBimhdaVkHyUXdSc4cI_cwzVKJWo6EeQGa3T&s=f4jusuGMobkL4UExY_gUM2padFMs1MqePaJH_7N9Ots&e=

6. https://urldefense.proofpoint.com/v2/url?u=https-3A__archive.midrange.com_rpg400-2Dl&d=DwIFAw&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=PCeNSf9fhcAUxJzoAZa9Db8EQ_28MTQACuQxevMqYzc&m=-Lo4vkMFad4Y7lYQ3lwhRG7BtzGPbBimhdaVkHyUXdSc4cI_cwzVKJWo6EeQGa3T&s=i_6H7b9sONvs61ZAjOsR-8FDEZma3cSacn4e0xhE1Sw&e=.

7. https://urldefense.proofpoint.com/v2/url?u=https-3A__lists.midrange.com_mailman_listinfo_rpg400-2Dl&d=DwIFAw&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=PCeNSf9fhcAUxJzoAZa9Db8EQ_28MTQACuQxevMqYzc&m=-Lo4vkMFad4Y7lYQ3lwhRG7BtzGPbBimhdaVkHyUXdSc4cI_cwzVKJWo6EeQGa3T&s=f4jusuGMobkL4UExY_gUM2padFMs1MqePaJH_7N9Ots&e=

8. https://urldefense.proofpoint.com/v2/url?u=https-3A__archive.midrange.com_rpg400-2Dl&d=DwIFAw&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=PCeNSf9fhcAUxJzoAZa9Db8EQ_28MTQACuQxevMqYzc&m=-Lo4vkMFad4Y7lYQ3lwhRG7BtzGPbBimhdaVkHyUXdSc4cI_cwzVKJWo6EeQGa3T&s=i_6H7b9sONvs61ZAjOsR-8FDEZma3cSacn4e0xhE1Sw&e=.

9. https://urldefense.proofpoint.com/v2/url?u=https-3A__www.mailguard.com.au_&d=DwIFAw&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=PCeNSf9fhcAUxJzoAZa9Db8EQ_28MTQACuQxevMqYzc&m=-Lo4vkMFad4Y7lYQ3lwhRG7BtzGPbBimhdaVkHyUXdSc4cI_cwzVKJWo6EeQGa3T&s=RagAUDXw-Yr_sppkQs2ZyOTpRhLcikZtHqYaYo6DTpE&e=

--

This is the RPG programming on IBM i (RPG400-L) mailing list

To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx<mailto:RPG400-L@xxxxxxxxxxxxxxxxxx>

To subscribe, unsubscribe, or change list options,

visit: https://urldefense.proofpoint.com/v2/url?u=https-3A__lists.midrange.com_mailman_listinfo_rpg400-2Dl&d=DwIFAw&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=PCeNSf9fhcAUxJzoAZa9Db8EQ_28MTQACuQxevMqYzc&m=-Lo4vkMFad4Y7lYQ3lwhRG7BtzGPbBimhdaVkHyUXdSc4cI_cwzVKJWo6EeQGa3T&s=f4jusuGMobkL4UExY_gUM2padFMs1MqePaJH_7N9Ots&e=

or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx<mailto:RPG400-L-request@xxxxxxxxxxxxxxxxxx>

Before posting, please take a moment to review the archives

at https://urldefense.proofpoint.com/v2/url?u=https-3A__archive.midrange.com_rpg400-2Dl&d=DwIFAw&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=PCeNSf9fhcAUxJzoAZa9Db8EQ_28MTQACuQxevMqYzc&m=-Lo4vkMFad4Y7lYQ3lwhRG7BtzGPbBimhdaVkHyUXdSc4cI_cwzVKJWo6EeQGa3T&s=i_6H7b9sONvs61ZAjOsR-8FDEZma3cSacn4e0xhE1Sw&e=.

Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx> for any subscription related questions.

----------

This email has been scanned for spam and viruses. Visit the following link to report this email as spam:

https://us-spambrella.cloud-protect.net/app/report_spam.php?mod_id=11&mod_option=logitem&report=1&type=easyspam&k=k1&payload=53616c7465645f5faca0a9b5e08c164a0e50897cdfe66abcce936ad7a603d0879ce221e858394b3faf77e954d0b4d2b9ff087c5d6b409b1a3dd5652ace27e7f84829179168715c23a226ae1f531fbdeaabcfe15628428101b5e60a6e993811a32d816fbc8087fc20f25b499f4ab77d4518739e366be2fe03b241ca2875c690e9d45685a83fc6970f03bed9c331a8e81f9d8da563af363a5a843814e8932c6c8d

This communication is intended only for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential, or otherwise exempt from disclosure under applicable law. If you are not the intended recipient, or the employee or agent responsible for delivering this communication to the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by telephone or email. The technical data herein provided is subject to export control under the International Traffic in Arms Regulations (ITAR) or the Export Administration Regulations (EAR). Such data should not be disclosed, exported or transferred in any manner to any foreign person or any foreign country without prior written approval of the Office of Defense Trade Controls, U.S. Department of State or the Bureau of Industry and Security, U.S. Department of Commerce. This email and any attachments may contain confidential and proprietary information and must be treated as such. In addition, the export or re-export of this information may be prohibited under applicable export control laws.