Re: Converting Character Zoned Data to Numeric in SQL -- MIDRANGE-L

Jonathan

I just had another idea - put the CASE into your SELECT, you might not need a UDF.

Use the ZONED function against the first n-1 characters, where n is the length of the number. Multiply it by 10. Use a CASE statement somewhat like yours in the UDF to add the appropriate value.

SELECT zoned(substr(field, 1, length(field) - 1)) * 10 +
Case substr(field, length(field), 1)
When '}' Then 0
When 'J' Then 1
When 'K' Then 2
When 'L' Then 3
When 'M' Then 4
When 'N' Then 5
When 'O' Then 6
When 'P' Then 7
When 'Q' Then 8
When 'R' Then 9
Else 0
End Case
from yourfile

Add another CASE to multiply by -1 or 1 based on the last character is not between 0 and 9.

Or apply the BITAND if it is a real function in a CASE.

Or maybe create a positive value - set the left nybble to 'F' with a BITOR, I think. Determine the sign by the BITAND.

Gotta try a couple of these myself - this is just hypothesizing!!!!

Vern

Vern Hamberg wrote:

How about creating a logical file over the flat file? I believe you can redefine a character substring as zoned.

Or I think there is a BITAND in SQL - if there is a consistent bit set off, you could test for BITAND of the last byte and the appropriate single-bit test value - should be zero if that one is off.

Or bite the bullet and use an external UDF that is written in RPG - then the conversion is as simple as using a data structure - probably.

Vern

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)
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
_______________________________________________________