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.