Hi,
The systools sercice validate_data is one way to detect invalid numeric values
... but it is very slow and returns only the first invalid numeric value in a row, i.e. if you have multiple numeric columns with invalid numeric values, you have to run in multiple times and can only fix one invalid numeric value per row.
I prefer to use the TRY_CAST command, with which I try to the numeric data into the numeric value.
If a columns includes an invalid numeric value a NULL value is returned, and NULL values can be trapped and selected:
With x as (Select <Key Columns or Id or RRN>
Try_Cast(NumCol1 as Dec(11, 0)) Col1,
Try_Cast(NumCol2 as Dec(7, 3)) Col2,
.....
Try_Cast(NumColN
From yourTable)
Select *
From x
Where Col1 is NULL or Col2 is NULL or ColN is NULL or ....
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: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Richard Schoen
Sent: Monday, 16 March 2026 15:30
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: Identifying Numeric Columns with Bad Data - S36 DDS Conversion
Hi All,
Doing a S36 conversion to add DDS/DDL to tables.
Is there a good methodology to check for decimal data errors on columns and fix them quickly ?
We do a CPYF *NOCHK to populate the tables.
And I’ve seen SQL function “systools.validate_data” as one possible method.
Thoughts appreciated.
Regards,
Richard Schoen
Web:
http://www.richardschoen.net
Email: richard@xxxxxxxxxxxxxxxxx
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
midrange.com
