Converting Character Zoned Data to Numeric in SQL

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
_______________________________________________________