October 23, 2017 at 10:08 am
Dear all,
I have a data conversion task and I am getting the following error message:
while converting column "VALUE_IN_FUND_CURR" (488) to column "VALUE_IN_FUND_CURR_NUMERIC" (267). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
The field values arfe below:
VALUE_IN_FUND_CURR |
-974.82 |
665.86 |
3'330'383.77 |
-891.13 |
NULL |
NULL |
NULL |
NULL |
8'771'456.32 |
1'882'120.90 |
NULL |
5'790'518.10 |
NULL |
-8'544.45 |
8'452.65 |
-28'344.12 |
1'653'797.50 |
100 |
NULL |
52'594.16 |
-0.01 |
-4'609.80 |
-11'066.71 |
-55'177.04 |
-866.1 |
-13'195.00 |
2'883.56 |
NULL |
633'358.62 |
826'495.18 |
5'000.80 |
7'349'927.09 |
NULL |
-7'976.19 |
NULL |
NULL |
2'577'813.18 |
66'588.11 |
-163.08 |
74'009.12 |
5'588.47 |
NULL |
-750'000.00 |
3'499'734.04 |
4'266'034.99 |
4'259'636.67 |
NULL |
3'699'000.00 |
3'940'331.29 |
-3'868.16 |
3'772'453.02 |
-34'688.01 |
411.84 |
-438.68 |
624'962.19 |
471'047.15 |
-471'047.64 |
5'178'680.19 |
-5'251.99 |
5'283'204.05 |
-5'345.50 |
5'351'410.10 |
699'568.77 |
766.98 |
-826'495.18 |
827.99 |
-8'771'456.40 |
-93'211.12 |
4'515'647.61 |
-2'782.00 |
2'242'327.68 |
2'750'714.07 |
1'119'345.90 |
10'908.67 |
NULL |
NULL |
NULL |
NULL |
NULL |
NULL |
NULL |
NULL |
NULL |
NULL |
NULL |
NULL |
NULL |
NULL |
NULL |
NULL |
NULL |
NULL |
NULL |
-1'144.13 |
276'630.63 |
-11'893'537.53 |
1'333'213.06 |
2'582.72 |
2'563'393.57 |
2'341'342.27 |
2'340'094.75 |
-23'284.64 |
2'302'650.69 |
2'256'742.70 |
-22'273.65 |
2'184'955.22 |
2'129'067.98 |
2'068'834.18 |
203.09 |
1'946.75 |
-1'845.67 |
18'287'384.41 |
16'642'216.82 |
-1'653'797.50 |
-16'273.61 |
-1'613.09 |
1'531'668.01 |
-13'841.08 |
1'332'088.79 |
998'267.09 |
Can someone help me undertand what is causing a numeric (28,12) to fail?
Thank you
October 23, 2017 at 11:06 am
Simple. Quote marks (') in the data. If the column is coming in as a string, you'll need to add a REPLACE function to the transformation to replace all single quote marks with nothing.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 23, 2017 at 12:00 pm
Hello, thank you for your answer. This was already done. It does not seem to be that. I dont know if its the range of the number or something else.
October 23, 2017 at 1:08 pm
river1 - Monday, October 23, 2017 12:00 PMHello, thank you for your answer. This was already done. It does not seem to be that. I dont know if its the range of the number or something else.
Of the above numbers, which is/are responsible for the failures?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 24, 2017 at 1:21 pm
river1 - Monday, October 23, 2017 12:00 PMHello, thank you for your answer. This was already done. It does not seem to be that. I dont know if its the range of the number or something else.
Then the only alternative is that one of the numbers is too large to be converted. Are you sure that it's one of the numbers you pasted? Here they all are, in "consumable form", and none of these failed to convert implicitly within T-SQL. I'm guessing it's a row you haven't posted, or, your rows with the word NULL are actually appearing as text in the column, and thus you have a different problem. Another way is that you've only posted the rows that successfully converted. I'm pretty sure none of the rows you posted is too large. So that everyone else can more easily consume what you posted, here's the data as a CREATE TABLE statement, followed by the necessary INSERT statement and VALUES clause:
CREATE TABLE #TESTIT (
VALUE_IN_FUND_CURR numeric(28,12)
);
INSERT INTO #TESTIT (VALUE_IN_FUND_CURR)
VALUES (-974.82),
(665.86),
(3330383.77),
(-891.13),
(NULL),
(NULL),
(NULL),
(NULL),
(8771456.32),
(1882120.90),
(NULL),
(5790518.10),
(NULL),
(-8544.45),
(8452.65),
(-28344.12),
(1653797.50),
(100),
(NULL),
(52594.16),
(-0.01),
(-4609.80),
(-11066.71),
(-55177.04),
(-866.1),
(-13195.00),
(2883.56),
(NULL),
(633358.62),
(826495.18),
(5000.80),
(7349927.09),
(NULL),
(-7976.19),
(NULL),
(NULL),
(2577813.18),
(66588.11),
(-163.08),
(74009.12),
(5588.47),
(NULL),
(-750000.00),
(3499734.04),
(4266034.99),
(4259636.67),
(NULL),
(3699000.00),
(3940331.29),
(-3868.16),
(3772453.02),
(-34688.01),
(411.84),
(-438.68),
(624962.19),
(471047.15),
(-471047.64),
(5178680.19),
(-5251.99),
(5283204.05),
(-5345.50),
(5351410.10),
(699568.77),
(766.98),
(-826495.18),
(827.99),
(-8771456.40),
(-93211.12),
(4515647.61),
(-2782.00),
(2242327.68),
(2750714.07),
(1119345.90),
(10908.67),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(-1144.13),
(276630.63),
(-11893537.53),
(1333213.06),
(2582.72),
(2563393.57),
(2341342.27),
(2340094.75),
(-23284.64),
(2302650.69),
(2256742.70),
(-22273.65),
(2184955.22),
(2129067.98),
(2068834.18),
(203.09),
(1946.75),
(-1845.67),
(18287384.41),
(16642216.82),
(-1653797.50),
(-16273.61),
(-1613.09),
(1531668.01),
(-13841.08),
(1332088.79),
(998267.09);
SELECT *
FROM #TESTIT;
DROP TABLE #TESTIT;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 24, 2017 at 10:24 pm
Thank you very much for your answer and support. Vou are right. I dont know how but the signs were still there even with the replace function. I have re written the replace and now works ok.
October 25, 2017 at 10:05 am
river1 - Tuesday, October 24, 2017 10:24 PMThank you very much for your answer and support. Vou are right. I dont know how but the signs were still there even with the replace function. I have re written the replace and now works ok.
Signs? ("-" character) or Quote marks ( ' character) ? Signs just make for negative numeric values, and shouldn't be a problem unless you were expecting all positive values. Quote marks, on the other hand, are never a part of numbers and can't be converted.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply