Ulrich

The reason is that the file being read is a flat file - a long character field. It probably was internally described in RPGII or RPGIII with I-specs that defined the fields for the program only.

Vern

Ulrich Krueger wrote:
I've been following this thread all day yesterday and today ... I'm just
wondering, why are you considering a negative zoned decimal value a
character string? Doesn't SQL, just like any other programming language on
the iSeries, support a native "numeric decimal" field type, both for input
as well as output? If so, then you don't need a special conversion function.
It's built in.


Regards,
Ulrich Krueger

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Monday, October 19, 2009 20:31
To: midrange-l@xxxxxxxxxxxx
Subject: Re: Converting Character Zoned Data to Numeric in SQL

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.


This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2020 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].