Re: detecting "bad data" using an sql statement

You could use regular expressions against packed and zoned fields to
determine if they are valid numbers.

Here is a pattern for a Packed field: ^(([0-9]{2})*[0-9][FD])$
Here is a pattern for a Zoned field: ^(([F][0-9])*[FD][0-9])$

You can use it this way:

select *
from mytable
where regexp_instr(hex(packedfield), '^(([0-9]{2})*[0-9][FD])$') <> 1
or regexp_instr(hex(zonedfield), '^(([F][0-9])*[FD][0-9])$') <> 1

On Wed, Jan 17, 2018 at 12:20 PM, 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