RE: SQL is numeric function

Hi Rob,

Thanks for the response. You're right, '123.45' would not get picked up as numeric. I like Birgitta's response about using %dec with a monitor around it.

To my taste, I prefer to only use the %dec test when the incoming text value has a period in it to avoid getting job log entries for values that are not numeric and don't contain periods. So I added in the following code after the %check.

// If not all numbers, but contains a period, then check to
// see if this is a number with a decimal.
ElseIf %scan( '.': checkText ) > 0;
Monitor;
number = %dec( checkText: 33: 9 );
rtnNumeric = *On;
On-Error;
rtnNumeric = *Off;
EndMon;

I'm glad my attempt to help resulted in others helping me as well.

-Kurt

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Tuesday, January 15, 2013 11:54 AM
To: Midrange Systems Technical Discussion
Subject: RE: SQL is numeric function

Gives an example of how to extend your rpg subprocedure to become a UDF and then be used by SQL but the rpg may be a bit simplistic. For example,
123.45 would not be numeric. Just adding a period to the check string may not be sufficient for the example another poster gave: 123.156.121.45 or some such thing. That's 'numeric' enough for an IP address but not numeric for mathematical functions.


Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: "Anderson, Kurt" <KAnderson@xxxxxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>,
Date: 01/15/2013 12:47 PM
Subject: RE: SQL is numeric function
Sent by: midrange-l-bounces@xxxxxxxxxxxx



Hi Joel,

I actually created my own and don't mind sharing.

Here's the function. xxxLIB is the library you'll be placing it in.
Create or Replace Function xxxlib/isNumeric ( VALUE VarChar(25) )
returns Char(1)
language rpgle
no sql
specific ccdlib/isNumericV
external name 'xxxLIB/TEXTPROCS($ISNUMERIC)'
parameter style general
;

I have a second overloaded function that handles being passed a number.
Sounds obvious I suppose, but meh.
Create or Replace Function xxxlib/isNumeric ( VALUE dec(25,9) )
returns char(1)
language sql
deterministic
specific xxxlib/isNumericN
return '1'
;

Here's the procedure to check if the input is numeric.
As you see in the procedure above, I have it in a service program called
TEXTPROCS (text procedures service program). You can put it wherever.


//=======================================================================*
// Procedure: $isNumeric *
// Description: Is the Input Numeric? *
// *
// Notes: This procedure is also wrapped in a SQL User
Defined *
// Function located in CCDLIB/QSQL(ISNUMERIC) *
// *
// Parameters: *
// Input - Text to test *
// Inp/Out - n/a *
// Output - n/a *
// *
// Return Value: '1' - Numeric *
// '0' - Not Numeric *
// *

//-----------------------------------------------------------------------*
P $isNumeric B Export
D PI n
D epText 25a Const Varying

// Local Constants
D NUMBERS C '0123456789'

// Local variables
D checkText S Static Like( epText )
D rtnNumeric S n Static

//-----------------------------------------------------------------------*
/free

checkText = %trim( epText );

// If the length is zero, it's not numeric.
If %len( checkText ) = 0;
rtnNumeric = *Off;

// If only numbers were found, it is numeric.
ElseIf %check( NUMBERS: checkText ) = 0;
rtnNumeric = *On;

// Otherwise it's not numeric.
Else;
rtnNumeric = *Off;
EndIf;

Return rtnNumeric;

/end-free
P $isNumeric E

//-----------------------------------------------------------------------*


- Kurt Anderson

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [
mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Stone, Joel
Sent: Tuesday, January 15, 2013 10:51 AM
To: midrange-l@xxxxxxxxxxxx
Subject: SQL is numeric function

Does Iseries SQL have a numeric test?

For example:

Runsql 'select * from ItemMaster where itemNbr is not numeric'



______________________________________________________________________
This outbound email has been scanned for all viruses by the MessageLabs
Skyscan service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.