Re: Bad numeric data

John,

You could either write a small RPGLE program and use a MONITOR/ENDMON group
or write a SQL function that checks if your number is valid .

Some years ago we had to write a small function in order to check some bad
data we had received from an external provider. Our function was something
like this :

----------------------------------------------------
DROP  Function QGPL/IsNumeric;

Create Function QGPL/IsNumeric (@TestData VarChar(64))
  Returns Char(1)
  Language SQL
  Deterministic
  Not Fenced
  Set Option Commit=*None, UsrPrf=*Owner

  BEGIN
   Declare @Double Double Not Null Default 0;

   Declare InvalidNum condition for '01565';
   Declare Exit Handler For InvalidNum
    Return 'N';
   If @TestData Is Null Then
    Return 'N';
   End If;

   Set @Double=Cast(@TestData As Double);
   Return 'Y';
  END
----------------------------------------------------

So you could something like:

UPDATE MyTable set MyField = 0
WHERE IsNumeric( cast(MyField  as varchar(64))    )  = 'N'


HTH,

Luis Rodriguez





Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries