Re: Converting Character Zoned Data to Numeric in SQL

[Luis Rodriguez <luisro58@xxxxxxxxx>]

New version. This one seems to work (had transposed two lines).

Again, my apologies.

------------------------------------
 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 Sign        Char(01);

   If Trim(ZChar) = '' THEN
    Return(0);
    END IF;

   Set Sign  = Right(Trim(ZChar), 1);
   Set ZChar = Left(Trim(ZChar), length(Trim(ZChar)) - 1);

   IF Sign IN ('M', 'N') THEN
      Return -Decimal(ZChar);
    ELSE
     Return Decimal(ZChar);
    END IF;

  END;
------------------------------------

Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries



On Mon, Oct 19, 2009 at 9:08 AM, Luis Rodriguez <luisro58@xxxxxxxxx> wrote:
> Disregard. Getting an incorrect value. My apologies (bad input data).
>
> Luis Rodriguez
> IBM Certified Systems Expert — eServer i5 iSeries
>
>
>
> On Mon, Oct 19, 2009 at 9:02 AM, Luis Rodriguez <luisro58@xxxxxxxxx> wrote:
> > 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.