RE: External RE: Count records in SQL
Jerry Forss <[email protected]> Wed, 18 Feb 2026 12:25:54 +0000
| Newsgroups | gmane.comp.lang.as400.rpg |
|---|---|
| Message-ID | <BL1PPF07C40B43A60350D9F92615FAE1975C66AA@BL1PPF07C40B43A.namprd22.prod.outlook.com> |
Don We as well have around 30 years of history. We have several environments, Test Dev and the like, so I will be able to give this a thorough workout. I will bring down the non-production environments down to 2 years after several test runs verifying everything works as expected. Yes, the first, and most difficult part of this project was convincing people we should do this. We do have and imaging system so they will always be able to bring up Order and Invoice PDF's and that they actually do not even use XA data after invoicing. Jerry -----Original Message----- From: RPG400-L <rpg400-l-bounces-+hD5IHI5Xscn3HwCXmMcX9BPR1lH4CV8@public.gmane.org> On Behalf Of Don Brown via RPG400-L Sent: Tuesday, February 17, 2026 5:37 PM To: RPG programming on IBM i <[email protected]> Cc: Don Brown <[email protected]> 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 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-+hD5IHI5Xscn3HwCXmMcX9BPR1lH4CV8@public.gmane.org> On Behalf Of Jerry Forss Sent: Tuesday, 17 February 2026 11:57 PM To: RPG programming on IBM i <[email protected]> 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-+hD5IHI5Xscn3HwCXmMcX9BPR1lH4CV8@public.gmane.org> On Behalf Of Birgitta Hauser Sent: Thursday, February 12, 2026 10:51 PM To: 'RPG programming on IBM i' <[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. 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-+hD5IHI5Xscn3HwCXmMcX9BPR1lH4CV8@public.gmane.org> On Behalf Of Jimmy Sansi Sent: Thursday, 12 February 2026 21:44 To: RPG programming on IBM i <[email protected]> Subject: Re: Count records in SQL What about ... exec sql GET DIAGNOSTICS :Rows = ROW_COUNT; [2]https://www.ibm.com/docs/en/i/7.5.0?topic=statements-get-diagnostics 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: [email protected] To subscribe, unsubscribe, or change list options, visit: [3]https://lists.midrange.com/mailman/listinfo/rpg400-l or email: RPG400-L-request-+hD5IHI5Xscn3HwCXmMcX9BPR1lH4CV8@public.gmane.org Before posting, please take a moment to review the archives at [4]https://archive.midrange.com/rpg400-l. Please contact support-FMtJrHiV//lnDLsaKlm4mFaTQe2KTcn/@public.gmane.org for any subscription related questions. -- 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: [5]https://lists.midrange.com/mailman/listinfo/rpg400-l or email: RPG400-L-request-+hD5IHI5Xscn3HwCXmMcX9BPR1lH4CV8@public.gmane.org Before posting, please take a moment to review the archives at [6]https://archive.midrange.com/rpg400-l. Please contact support-FMtJrHiV//lnDLsaKlm4mFaTQe2KTcn/@public.gmane.org for any subscription related questions. -- 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: [7]https://lists.midrange.com/mailman/listinfo/rpg400-l or email: RPG400-L-request-+hD5IHI5Xscn3HwCXmMcX9BPR1lH4CV8@public.gmane.org Before posting, please take a moment to review the archives at [8]https://archive.midrange.com/rpg400-l. Please contact support-FMtJrHiV//lnDLsaKlm4mFaTQe2KTcn/@public.gmane.org for any subscription related questions. -- Message protected by MailGuard: e-mail anti-virus, anti-spam and content filtering. [9]https://www.mailguard.com.au References Visible links 1. https://www.oneteamit.com.au/ 2. https://www.ibm.com/docs/en/i/7.5.0?topic=statements-get-diagnostics 3. https://lists.midrange.com/mailman/listinfo/rpg400-l 4. https://archive.midrange.com/rpg400-l. 5. https://lists.midrange.com/mailman/listinfo/rpg400-l 6. https://archive.midrange.com/rpg400-l. 7. https://lists.midrange.com/mailman/listinfo/rpg400-l 8. https://archive.midrange.com/rpg400-l. 9. https://www.mailguard.com.au/ -- 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: RPG400-L-request-+hD5IHI5Xscn3HwCXmMcX9BPR1lH4CV8@public.gmane.org Before posting, please take a moment to review the archives at https://archive.midrange.com/rpg400-l. Please contact support-FMtJrHiV//lnDLsaKlm4mFaTQe2KTcn/@public.gmane.org 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 www.walzcraft.com<https://www.walzcraft.com> Phone: 1-800-237-1326 -- 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: RPG400-L-request-+hD5IHI5Xscn3HwCXmMcX9BPR1lH4CV8@public.gmane.org Before posting, please take a moment to review the archives at https://archive.midrange.com/rpg400-l. Please contact support-FMtJrHiV//lnDLsaKlm4mFaTQe2KTcn/@public.gmane.org for any subscription related questions.