Re: Converting Character Zoned Data to Numeric in SQL

I've been following this thread all day yesterday and today ... I'm just
wondering, why are you considering a negative zoned decimal value a
character string? Doesn't SQL, just like any other programming language on
the iSeries, support a native "numeric decimal" field type, both for input
as well as output? If so, then you don't need a special conversion function.
It's built in.


Regards,
Ulrich Krueger

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Monday, October 19, 2009 20:31
To: midrange-l@xxxxxxxxxxxx
Subject: Re: Converting Character Zoned Data to Numeric in SQL

The following function was created and verified on v5r2. The WorkChar and the Decimal cast outside the multiplication were both required to get the UDF to function correctly on the system I used, even though they should not be required; i.e. the commented return or even returning the expression for WorkChar * Sign should work on a more recent release.

<code>
create function c15toz15 (InZoned varchar(15) for bit data)
RETURNS DEC(15,0) LANGUAGE SQL SPECIFIC CVTZONED DETERMINISTIC
MODIFIES SQL DATA CALLED ON NULL INPUT DISALLOW PARALLEL
SET OPTION DBGVIEW=*LIST
BEGIN
declare WorkChar VarCHar(15);
declare SignByte Char(1);
declare Sign dec(1, 0) default 1;
Set SignByte=right(InZoned, 1);
If SignByte BETWEEN x'D0' AND x'D9'
or SignByte BETWEEN x'B0' AND x'B9'
Then Set Sign = -1; End If;
Set WorkChar = left(InZoned, length(InZoned)-1)
concat LOR(SignByte, x'F0');
/* Return Decimal( WorkChar * Sign, 15, 0) ; */
Return Decimal(WorkChar, 15, 0) * Sign ;
END
</code>

Regards, Chuck

Jonathan Mason 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)
<<SNIP>>

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.