If you have WDSC or RDI you could set a service entry point on the UDF.


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jonathan Mason
Sent: Monday, October 19, 2009 7:11 AM
To: midrange-l@xxxxxxxxxxxx
Subject: Converting Character Zoned Data to Numeric in SQL

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

Privileged and Confidential. This e-mail, and any attachments there to, is intended only for use by the addressee(s) named herein and may contain privileged or confidential information. If you have received this e-mail in error, please notify me immediately by a return e-mail and delete this e-mail. You are hereby notified that any dissemination, distribution or copying of this e-mail and/or any attachments thereto, is strictly prohibited.

This thread ...

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].