number formatting problem

[sparks]

I am trying to insert data into another database

problem is one column in a table is float .375, .09, .064
destination is decimal(5,1)

should I change the destination to decimal(5,3) or to a float?
I looked at the values in destination and it is 100.0 , 1000.0
,1.0,0.6
like this so I am conserned about reformatting the destination
correctly

[Gene Wirchenko]

Post by sparks
I am trying to insert data into another database
problem is one column in a table is float .375, .09, .064
destination is decimal(5,1)
should I change the destination to decimal(5,3) or to a float?
I looked at the values in destination and it is 100.0 , 1000.0
,1.0,0.6
like this so I am conserned about reformatting the destination
correctly

Is there a limit to the number of digits after the decimal point?
If there is, fixed decimal will work fine if you specify enough
decimal places. If not, then use floating point.

1000.0 will not fit in decimal(5,3). It would have to be
decimal(7+,3). (If you add decimal places (the right-hand number),
you have to increase the overall size (the left-hand number), or some
numbers will no longer fit.)

Sincerely,

Gene Wirchenko

[Bob Barrows]

Post by sparks
I am trying to insert data into another database
problem is one column in a table is float .375, .09, .064
destination is decimal(5,1)
should I change the destination to decimal(5,3) or to a float?
I looked at the values in destination and it is 100.0 , 1000.0
,1.0,0.6
like this so I am conserned about reformatting the destination
correctly

There is never a need to change the destination. Convert the data to the
proper precision and scale when inserting it.

insert table (decimalcol)
select cast(floatcol as decimal(5,1)) from sourcetable

[sparks]

this works fine except when the destination is an int.
What would you do then?


On Fri, 30 Mar 2012 17:09:41 -0400, "Bob Barrows"

Post by Bob Barrows
insert table (decimalcol)
select cast(floatcol as decimal(5,1)) from sourcetable

[Bob Barrows]

You consult with the owner of the data and ask whether it is acceptable to
lose the decimal portion of the data. if not, then you need to explain that
you need to change the datatype of the destination to a type that allows the
persistance of the decimal portion.. The data's owner might be more amenable
to the changing of the data than he is to the changing of the schema. He
needs to make that decision, not you.

Post by sparks
this works fine except when the destination is an int.
What would you do then?
On Fri, 30 Mar 2012 17:09:41 -0400, "Bob Barrows"

Post by Bob Barrows
insert table (decimalcol)
select cast(floatcol as decimal(5,1)) from sourcetable

[sparks]

Yep I told them that I could not put a real into an int, smallint or
anything else and still keep the ORIGINAL value.
IF they had a way I would send it to them and they could do it.

uh ok then change them.

typical

thanks to all of you for the help and support :)


On Tue, 3 Apr 2012 12:16:58 -0400, "Bob Barrows"

Post by Bob Barrows
You consult with the owner of the data and ask whether it is acceptable to
lose the decimal portion of the data. if not, then you need to explain that
you need to change the datatype of the destination to a type that allows the
persistance of the decimal portion.. The data's owner might be more amenable
to the changing of the data than he is to the changing of the schema. He
needs to make that decision, not you.

Post by sparks
this works fine except when the destination is an int.
What would you do then?
On Fri, 30 Mar 2012 17:09:41 -0400, "Bob Barrows"

Post by Bob Barrows
insert table (decimalcol)
select cast(floatcol as decimal(5,1)) from sourcetable