Problem converting VARCHAR to NUMERIC or DECIMAL

Question

Problem converting VARCHAR to NUMERIC or DECIMAL

hal9000-869914

SSC Enthusiast

Points: 169
More actions
August 23, 2009 at 3:56 am

#141105

Hi,
i have a varchar(50) column containing data like this
0.00000
100.000
151.350
The SAP sepecification says that it is a DECIMAL 9,5 field.
Now I have the problem that I can't load the data from one table to another with a column of type numeric 9,5. I am getting the error:
... The value could not be converted because of a potential loss of data. ...
Please Help.

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

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

hal9000-869914 SSC Enthusiast Points: 169 More actions · Answer 1

I think I found the problem. The problem is that the varchar column uses a point as decimal separator. The destination numeric column expects a comma as seperator. Is there a common way to solve this problem.

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

Setting up your data and testing I do not find a problem... Can you

explain in more detail? I am assuming (and that may be my mistake),

that SAP 9.5 defines a decimal/numeric value as DECIMAL(p.s)

Where p (precision) is defined as:

The maximum total number of decimal digits that can be stored, both to

the left and to the right of the decimal point.

s (scale)

The maximum number of decimal digits that can be stored to the right of

the decimal point.

CREATE TABLE #T1(Val VARCHAR(50))

GO

INSERT INTO #T1

SELECT '0.00000' UNION ALL

SELECT '100.000' UNION ALL

SELECT '151.350'

GO

CREATE TABLE #T2 (VAL NUMERIC(9,5))

GO

INSERT INTO #T2

SELECT CAST(Val AS NUMERIC(9,5)) FROM #T1

GO

SELECT Val FROM #T2

Completed without an error or warning message, even when I increased the 100.000 to 100.123456 in which case the value inserted into the table #T2 was rounded up to 100.12346

However if I have a VARCHAR value of '12345.00000' I will then get

Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting varchar to data type numeric.

That of course can be corrected by increasing the numeric value to (10,5)

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Michael Tocik SSCertifiable Points: 7043 More actions · Answer 3

Hi

Out of curiosity what version of SAP are you using with what RDBMS?

hal9000-869914 SSC Enthusiast Points: 169 More actions · Answer 4

I think I solved the problem. In my SSIS package the locale was set to German(Germany). So I think the convert expects a "," as decimal separator. I switched the locale to German(Switzerland) and everything was working fine.

Another problem was in another column that I had to remove the thousand separators.

I don't know which version of SAP it is. I am getting a simple flat file export.