Re: Converting Character Zoned Data to Numeric in SQL

[Luis Rodriguez <luisro58@xxxxxxxxx>]

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.