Re: detecting "bad data" using an sql statement

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