Convert Mainframe numbers

Question

Post reply

Convert Mainframe numbers

lnelson

Mr or Mrs. 500

Points: 522
More actions
September 11, 2011 at 9:56 am

#243018

I have a table (checkmaster) which was created by importing from an IBM mainframe system. The numeric values are in the mainframe format (stored as varchar(50) in my table I imported to) as follows:
Emp No HoursWorked
00532210 000000000000000
00535713 -0000000000005325
00679463 000000000000500
I need to convert these to decimal numbers for users to report on. I tried using
UPDATE CheckMaster SET HoursWorked = STR(CONVERT(DECIMAL(10,2),HoursWorked)/100,8,2)
Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting varchar to data type numeric.
There are overe 2 million rows and I checked the HoursWorked field by using IsNumeric and everything is coming back as numeric so I'm not sure what is causing this problem. I also tried selecting only the rows that did not have the "-" or the all zero and I still get the same error.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 1

Hi

I don't get any error when testing your sample data.

; WITH data (EmpNo, HoursWorked) AS (

SELECT '00532210', '000000000000000'

UNION ALL SELECT '00535713', '-0000000000005325'

UNION ALL SELECT '00679463', '000000000000500'

)

SELECT STR(CONVERT(DECIMAL(10,2),HoursWorked)/100,8,2)

FROM data

Greets

Flo

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

lnelson (9/11/2011)
I have a table (checkmaster) which was created by importing from an IBM mainframe system. The numeric values are in the mainframe format (stored as varchar(50) in my table I imported to) as follows:
Emp No HoursWorked
00532210 000000000000000
00535713 -0000000000005325
00679463 000000000000500
I need to convert these to decimal numbers for users to report on. I tried using
UPDATE CheckMaster SET HoursWorked = STR(CONVERT(DECIMAL(10,2),HoursWorked)/100,8,2)
Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting varchar to data type numeric.
There are overe 2 million rows and I checked the HoursWorked field by using IsNumeric and everything is coming back as numeric so I'm not sure what is causing this problem. I also tried selecting only the rows that did not have the "-" or the all zero and I still get the same error.

You may still have "bad" data in the column even though you checked it with ISNUMERIC because ISNUMERIC will allow a significant number of characters as "numeric related" characters to pass through. ISNUMERIC should never be used as an "ISALLDIGITS" function. Please see the following article for much more informtion on the subject.

http://www.sqlservercentral.com/articles/IsNumeric/71512/

You might also want to reconsider and NOT use STR... because of an interim conversion to the FLOAT datatype (which may cause numbers that you don't expect) and a performance problem, you will likely be better off using a straight CAST or CONVERT. Please see the following article for more on that subject.

http://www.sqlservercentral.com/articles/T-SQL/71565/

Last but certainly not least, I'm not sure why you actually need to convert the numeric value that you calculate back to a string, again. It's just going to slow down your original conversion and cause many implicit conversions (along with some related performance problems) if you actually used it to do any mathematical calculations.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

lnelson Mr or Mrs. 500 Points: 522 More actions · Answer 3

Thanks everyone for the feedback. Sometimes you just make things difficult on yourself which is what happened in this case. I simply changed the datatype in the table from varchar(50) to numeric(18,2). It let me save it but it converted everything that was 000000000004075 to 4075.00. Then I did an update on the column to divide by 100 and all is good.