RE: detecting "bad data" using an sql statement

For updating the invalid numeric values, you may use the REGEXP_LIKE
Predicate in composition with the Regex Expressions Mark posted in another
eMail and check the hex dates.

Something like this:
1. Zoned Fields:
Update YourTable
Set YourColumn = -999 -- or any other default value
Where Not RegexP_Like(Hex(YourColumn), '^(([F][0-9])*[FD][0-9])$');

2. Packed Fields
Update YourTable
Set YourColumn = -999 -- or any other default value
Where Not RegexP_Like(Hex(YourColumn), '^(([0-9]{2})*[0-9][FD])$ ');

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"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)


-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Luis
Rodriguez
Sent: Freitag, 19. Januar 2018 10:56
To: RPG programming on the IBM i (AS/400 and iSeries)
<rpg400-l@xxxxxxxxxxxx>
Subject: Re: detecting "bad data" using an sql statement

Jay,

SQL just won't let you retrieve a column with bad data, so either the Select
a.* or the Select nopmt will give you problems.

What you must do is a SELECT with "non suspect" columns that allows you to
identify the bad record and then try to fix it (or clear the column at
once). Maybe SELECT the record key or the RRN.

Regards,
Luis

Sent from my Moto G phone. Please excuse my brevity.


On Jan 18, 2018 8:43 PM, "Jay Vaughn" <jeffersonvaughn@xxxxxxxxx> wrote:

Luis,

neither of these work...

SELECT nopmt
FROM ls#jhv/srvncp
WHERE isValNum(nopmt) is null
Selection error involving field NOPMT.


SELECT a.*
FROM ls#jhv/srvncp a
WHERE isValNum(a.nopmt) is null
Selection error involving field NOPMT.

Select or omit error on field SRVNCP_1.NOPMT member SRVNCP.
Select or omit error on field SRVNCP_1.NOPMT member SRVNCP.

Message ID . . . . . . : CPD4019 Severity . . . . . . . : 10
Message type . . . . . : Diagnostic
Date sent . . . . . . : 01/18/18 Time sent . . . . . . : 19:38:31

Message . . . . : Select or omit error on field SRVNCP_1.NOPMT member
SRVNCP.
Cause . . . . . : A select or omit error occurred in record 4, record form
*FIRST, member number 1 of file SRVNCP in library LS#JHV, because of
condition 1 of the following conditions:
1 - The data was not valid in a decimal field.
2 - A select or omit program error occurred because the data in a select
or omit field is not compatible with the select or omit specifications.
3 - A select or omit program call error occurred.
4 - A comparison was tried on a floating point field that was not a
number.
5 - The data was not valid in a double-byte character set (DBCS) field.

On Wed, Jan 17, 2018 at 1:15 PM, Luis Rodriguez <luisro58@xxxxxxxxx> wrote:

Jay,

I don't think that the problem is with your function call. My
"Numeric(1, 5)" was just a way to define a big enough column for our data.

I believe that you are having problems with the "SELECT *", as it
must be including an invalid LNAMT value. Try to do a SELECT that does
not include the LNAMT column.

Regards,
Luis

Luis Rodriguez

--


On 17 January 2018 at 13:20, Jay Vaughn <jeffersonvaughn@xxxxxxxxx> wrote:

Luis, so i tried your function and i'm getting the below error...

select * from ls#Jhv/srvncp
where lscnvtools.isvalnum(lnamt) is null Selection error involving
field LNAMT.

lnamt is numeric(9,2)... do you see my issue?


create or replace function
lscnvtools/IsValNum (i_chkNumber numeric(31,5)
)
Returns Numeric(31, 5)
Language SQL
Returns null on null input
Deterministic
Not Fenced
Set Option Commit=*None, UsrPrf=*Owner

Begin
Declare OutNumber Numeric(31, 5) ;
Declare InvalidNum condition for '01565';
Declare Exit Handler For InvalidNum return null;
Return i_chkNumber;
END











On Fri, Jan 12, 2018 at 9:15 AM, Luis Rodriguez <luisro58@xxxxxxxxx>
wrote:

Jay,

You could write a small function that returns, say, a NULL value
when

the

data is invalid. Some years ago we needed to do this and created
the following function:

Create Function mylib.isnumber (ChkNumber Numeric(31, 5)) Returns
Numeric(31, 5) Language SQL Returns null on null input
Deterministic Not Fenced Set Option Commit=*None, UsrPrf=*Owner

Begin
Declare OutNumber Numeric(31, 5) ;
Declare InvalidNum condition for '01565';
Declare Exit Handler For InvalidNum return null;
Return ChkNumber;
END

Just run a SELECT a.* FROM myTable a WHERE isNumber(a.Number) is

null.

HTH,
Luis


Luis Rodriguez

--


On 12 January 2018 at 10:01, Jay Vaughn
<jeffersonvaughn@xxxxxxxxx>

wrote:

So the only way i know to detect bad data using sql is when
doing

the

actual insert of the data into another file.

Is there "select" way of doing it instead? And ideally report
all

rows

back that have bad data detected. also when a select is done in

STRSQL

over the file, the bad data shows up as "+++++", so just need to

know

how

STRSQL detects it and shows "+++++" instead of the data.

example...

insert into ls#jhv.srvfm50 (prefix) (select prefix from
ldataicl.srvfm50)

file ls#Jhv.srvfm50 has "bad data" in it and the following sql
error

occurs

when the statement is run...

Message ID . . . . . . : SQL0406





Message . . . . : Conversion error on assignment to column PREFIX.



Cause . . . . . : During an attempt to assign a value to column

PREFIX

with
an INSERT, UPDATE, ALTER TABLE, or REFRESH TABLE statement,

conversion

error
type 6 occurred. If precompiling, the error occurred when

converting

a

numeric constant to the same attributes as column PREFIX. A
list

of

the

error types follows:

-- Error type 1 is overflow.

-- Error type 2 is floating point overflow.

-- Error type 3 is floating point underflow.

-- Error type 4 is a floating point conversion error.

-- Error type 5 is not an exact result.

-- Error type 6 is numeric data that is not valid.
--
This is the RPG programming on the IBM i (AS/400 and iSeries)

(RPG400-L)

mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please
take a moment to review the archives at
https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our

affiliate

link: http://amzn.to/2dEadiD

--
This is the RPG programming on the IBM i (AS/400 and iSeries)

(RPG400-L)

mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please
take a moment to review the archives at
https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our
affiliate
link: http://amzn.to/2dEadiD

--
This is the RPG programming on the IBM i (AS/400 and iSeries)
(RPG400-L) mailing list To post a message email:
RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list
options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at
https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our
affiliate
link: http://amzn.to/2dEadiD

--
This is the RPG programming on the IBM i (AS/400 and iSeries)
(RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at
https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD

--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link:
http://amzn.to/2dEadiD