Re: Converting Character Zoned Data to Numeric in SQL

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.