Re: Converting Character Zoned Data to Numeric in SQL -- MIDRANGE-L

Disregard. Getting an incorrect value. My apologies (bad input data).

Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries

On Mon, Oct 19, 2009 at 9:02 AM, Luis Rodriguez <luisro58@xxxxxxxxx> wrote:

Jonathan,

This works for me:

-------------------------------------------------
DROP Function @520/CVTZoned;

Create Function @520/CVTZoned (ZChar char(15))
Returns Dec(15, 0)
Language SQL
Deterministic
Not Fenced
No External Action

BEGIN
Declare DecValue Decimal(15, 0);
Declare Sign Char(01);

If Trim(ZChar) = '' THEN
Return(0);
END IF;

Set DecValue = 0;
Set ZChar = Substr(Trim(ZChar), 1, length(Trim(ZChar)) - 1);
Set Sign = Substr(Trim(ZChar), length(Trim(ZChar)), 1);

IF Sign IN ('M', 'N') THEN
Return -Decimal(ZChar);
ELSE
Return Decimal(ZChar);
END IF;

END;
-------------------------------------------------

BTW, in our system (V5R3) , a negative ZONED field registers as N.

HTH,

Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries

On Mon, Oct 19, 2009 at 7:41 AM, Jonathan Mason
<jonathan.mason@xxxxxxxxxxxxxxxx> wrote:

We have a flat file that is used to interface data with a third party
and have been asked to split the file into three smaller versions. The
file contains a header record, detail records and a footer record with
total amounts on it.

I can split the file easily enough and add in a footer record, but I
need to update the footer with the totals for the new, smaller, file.
The trouble is that negative values are held in "zoned" format, so -1234
is stored as "123M".

Is it possible in SQL to convert a character "zoned" value to numeric?
I've tried putting a UDF together (see below), but it keeps hitting the
exception handler whenever I run it for negative numbers:

CREATE FUNCTION CVTZONED (INZONED VARCHAR(15))
RETURNS DEC(15,0)
LANGUAGE SQL
SPECIFIC CVTZONED
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
DISALLOW PARALLEL
Set Option DBGVIEW=*LIST
BEGIN

Declare WorkChar VarChar(15);
Declare Sign Char(1);
Declare WorkSign Char(1);
Declare RealValue Dec(15,0);

Declare Continue Handler for SQLEXCEPTION
Set RealValue = 0;

Set Sign = '+';
Set WorkSign = Substring(InZoned,Char_Length(InZoned),1);

If WorkSign >= '}' and WorkSign <= 'R' Then
Set Sign = '-';
Case
When WorkSign = '}' Then Set WorkSign = '0';
When WorkSign = 'J' Then Set WorkSign = '1';
When WorkSign = 'K' Then Set WorkSign = '2';
When WorkSign = 'L' Then Set WorkSign = '3';
When WorkSign = 'M' Then Set WorkSign = '4';
When WorkSign = 'N' Then Set WorkSign = '5';
When WorkSign = 'O' Then Set WorkSign = '6';
When WorkSign = 'P' Then Set WorkSign = '7';
When WorkSign = 'Q' Then Set WorkSign = '8';
When WorkSign = 'R' Then Set WorkSign = '9';
Else Set WorkSign=0;
End Case;

Set WorkChar =
Concat(Left(InZoned,Char_Length(InZoned)-1),WorkSign);
Else
Set WorkChar = InZoned;
End If;

Set RealValue = Zoned(InZoned);
If Sign = '-' Then
Set RealValue = RealValue * -1
End If;

Return RealValue;

END

Any advice on how I can achieve the result I want, or debug the UDF to
see where it's failing would be most gratefully welcome.

Thanks

Jonathan

Jonathan Mason
iSeries Consultant
www.astradyne-uk.com

_______________________________________________________
This message was sent using NOCC v1.14 webmail software
_______________________________________________________

--
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.