DECLARE @foo VARCHAR(9)
SET @foo = '12451520'

SELECT 'before', @foo
SELECT 'after (string)', LEFT(@foo, (LEN(@foo) -4)) + '.' + RIGHT(@foo, 4)

SELECT 'after (DECIMAL(8,4))' , CONVERT(DECIMAL(8,4), (LEFT(@foo, (LEN(@foo) -4)) + '.' + RIGHT(@foo, 4)))

quote:

---

Originally posted by visakh16

you cant change it to 8 4 unless you change precision and scale in destination table. it always tries to map the type to destination field and will throw the error.

---

quote:

---

Originally posted by lepeniotis

If you have an overflow proble why don't you try to atler the decimal with float? Can you try this??
Regards

Panagiotis

MSc Advanced Computing Science
MSc Database Professional
Sheffield Hallam University
MCP (70-229, 70-228)
Industrial IT Engineer

---

quote:

---

Originally posted by Transact Charlie

There's probably a better way than this but you could.

insert all the values into a column in a temp table or similar of datatype VARCHAR(<suitable length>) [probably 9 if all your numbers are 8 digits wide]

Then issue an UPDATE to add the decimal into the string.

Finally, put that into your decimal 8,4 column (either taking advantage of an implicit conversion or by explicit cast).

example (for 1 variable)

DECLARE @foo VARCHAR(9)
SET @foo = '12451520'

SELECT 'before', @foo
SELECT 'after (string)', LEFT(@foo, (LEN(@foo) -4)) + '.' + RIGHT(@foo, 4)

SELECT 'after (DECIMAL(8,4))' , CONVERT(DECIMAL(8,4), (LEFT(@foo, (LEN(@foo) -4)) + '.' + RIGHT(@foo, 4)))

As I said there are no doubt many better ways to do this but, if you need to do this just once then who cares about performance! (as long as it doesn't crawl)

-------------
Charlie

---

quote:

---

Originally posted by kote_alex

quote:

---

Originally posted by lepeniotis

If you have an overflow proble why don't you try to atler the decimal with float? Can you try this??
Regards

Panagiotis

MSc Advanced Computing Science
MSc Database Professional
Sheffield Hallam University
MCP (70-229, 70-228)
Industrial IT Engineer

---

Hmmm... float? u mean I should put float in the connection manager instead of decimal .... or in the table ... or bouth?

---

quote:

---

Originally posted by kote_alex

quote:

---

Originally posted by Transact Charlie

There's probably a better way than this but you could.

insert all the values into a column in a temp table or similar of datatype VARCHAR(<suitable length>) [probably 9 if all your numbers are 8 digits wide]

Then issue an UPDATE to add the decimal into the string.

Finally, put that into your decimal 8,4 column (either taking advantage of an implicit conversion or by explicit cast).

example (for 1 variable)

DECLARE @foo VARCHAR(9)
SET @foo = '12451520'

SELECT 'before', @foo
SELECT 'after (string)', LEFT(@foo, (LEN(@foo) -4)) + '.' + RIGHT(@foo, 4)

SELECT 'after (DECIMAL(8,4))' , CONVERT(DECIMAL(8,4), (LEFT(@foo, (LEN(@foo) -4)) + '.' + RIGHT(@foo, 4)))

As I said there are no doubt many better ways to do this but, if you need to do this just once then who cares about performance! (as long as it doesn't crawl)

-------------
Charlie

---

I'm not sure I get what u wrote... "@foo" ? collum name? or table name ? ... and also my file contains 2 million rows... and I have bigger lengths of collums than 9 ...

There must be a way using visual studio ... some data types or delimitations....

---

quote:

---

Originally posted by Transact Charlie

quote:

---

Originally posted by kote_alex

quote:

---

Originally posted by Transact Charlie

There's probably a better way than this but you could.

insert all the values into a column in a temp table or similar of datatype VARCHAR(<suitable length>) [probably 9 if all your numbers are 8 digits wide]

Then issue an UPDATE to add the decimal into the string.

Finally, put that into your decimal 8,4 column (either taking advantage of an implicit conversion or by explicit cast).

example (for 1 variable)

DECLARE @foo VARCHAR(9)
SET @foo = '12451520'

SELECT 'before', @foo
SELECT 'after (string)', LEFT(@foo, (LEN(@foo) -4)) + '.' + RIGHT(@foo, 4)

SELECT 'after (DECIMAL(8,4))' , CONVERT(DECIMAL(8,4), (LEFT(@foo, (LEN(@foo) -4)) + '.' + RIGHT(@foo, 4)))

As I said there are no doubt many better ways to do this but, if you need to do this just once then who cares about performance! (as long as it doesn't crawl)

-------------
Charlie

---

I'm not sure I get what u wrote... "@foo" ? collum name? or table name ? ... and also my file contains 2 million rows... and I have bigger lengths of collums than 9 ...

There must be a way using visual studio ... some data types or delimitations....

---

Hi.

From the description of your problem I gathered that. (tell me where I am wrong)

1) Whatever datasource is proving your data is spitting out either an integer or a string with no decimal place? So you start with a value like : '33421233' ?

2) you KNOW that really should represent '3342.1233' ?

3) you have a flat file (csv file?) with these numbers (so they will be strings)

4) You want to turn them into DECIMAL(8,4) with the 4 RIGHTMOST digits becoming the figures after the (.) ?

5) I suggested that you could import the column as datatype (VARCHAR(9)) rather than DECIMAL(8,4). Then do processing on the strings to insert a decimal point into the characterposition 5 from the end of the string. So '33421233' would become '3342.1233'

6) You could then insert this data (from your table in the database) into the actual table you wish and it will correctly give you width 8 and 4 precision.

The code snippet was just an example of the process for 1 variable.
Charlie.

-------------
Charlie

---

quote:

---

Originally posted by kote_alex


Another problem ... when i make the conversion to decimal 8,4 it gives me the erorr message that i overflowed the destination collum... so i changed it to 9,4 in my table ... still no change... then 10 , 4 and it worked ... but still no change in moving the comma... so i still have 269815.0000 not 2698.1500...:( ... what should i do?

---

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by kote_alex


Another problem ... when i make the conversion to decimal 8,4 it gives me the erorr message that i overflowed the destination collum... so i changed it to 9,4 in my table ... still no change... then 10 , 4 and it worked ... but still no change in moving the comma... so i still have 269815.0000 not 2698.1500...:( ... what should i do?

---

thats basically becuse it cant understand the decimal point correctly while reading from sourcedata.Can you see if its recognizing data correctly (along with decimal) in preview tab of source task?

---

quote:

---

Originally posted by kote_alex

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by kote_alex


Another problem ... when i make the conversion to decimal 8,4 it gives me the erorr message that i overflowed the destination collum... so i changed it to 9,4 in my table ... still no change... then 10 , 4 and it worked ... but still no change in moving the comma... so i still have 269815.0000 not 2698.1500...:( ... what should i do?

---

thats basically becuse it cant understand the decimal point correctly while reading from sourcedata.Can you see if its recognizing data correctly (along with decimal) in preview tab of source task?

---

no ! it doesn't recognize decimal... I exported all data form flat file source to a temp table as varchar with the corespondent length...
after that I made another data flow task that copies data from temp table to my table ... and I've inserted a daca conversion task in between... so a database source,data conversion,database destination...
data conversion... is set for decimal scale of 8... it just adds 4x"0" it doesn't split the number... the destination colum is set as money...
if i try seting conversion to numeric 8.4 then i get the following
{ [OLE DB Destination 1 1 [11]] Error: There was an error with input column "Copy of OBHPRZ" (773) on input "OLE DB Destination Input" (24). The column status returned was: "Conversion failed because the data value overflowed the specified type.". }

If i change precision to 10 and scale of 4 to conversion then it work but it still adds 4x'0'to row instead of spliting it.

please help !

---

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by kote_alex

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by kote_alex


Another problem ... when i make the conversion to decimal 8,4 it gives me the erorr message that i overflowed the destination collum... so i changed it to 9,4 in my table ... still no change... then 10 , 4 and it worked ... but still no change in moving the comma... so i still have 269815.0000 not 2698.1500...:( ... what should i do?

---

thats basically becuse it cant understand the decimal point correctly while reading from sourcedata.Can you see if its recognizing data correctly (along with decimal) in preview tab of source task?

---

no ! it doesn't recognize decimal... I exported all data form flat file source to a temp table as varchar with the corespondent length...
after that I made another data flow task that copies data from temp table to my table ... and I've inserted a daca conversion task in between... so a database source,data conversion,database destination...
data conversion... is set for decimal scale of 8... it just adds 4x"0" it doesn't split the number... the destination colum is set as money...
if i try seting conversion to numeric 8.4 then i get the following
{ [OLE DB Destination 1 1 [11]] Error: There was an error with input column "Copy of OBHPRZ" (773) on input "OLE DB Destination Input" (24). The column status returned was: "Conversion failed because the data value overflowed the specified type.". }

If i change precision to 10 and scale of 4 to conversion then it work but it still adds 4x'0'to row instead of spliting it.

please help !

---

instead of data conversion try if you can get decimal bit by using derived column task if source data can be consistently converted to form you want.

---

quote:

---

Originally posted by visakh16

suppose you get data as 26981500 from flat file. then add a derived column task such that value is sourcecol/10000 and give replace original column option. then use this new column to map to destination table. then you should get 2698.15

---

quote:

---

Originally posted by kote_alex

quote:

---

Originally posted by visakh16

suppose you get data as 26981500 from flat file. then add a derived column task such that value is sourcecol/10000 and give replace original column option. then use this new column to map to destination table. then you should get 2698.15

---

so in the expresion tab i should write : sourcecol/'column name' replace 'column name'

---