Re: Converting Character Zoned Data to Numeric in SQL

[CRPence <CRPbottle@xxxxxxxxx>]

  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.