September 22, 2004 at 12:51 pm
Some external customer is providing me some decimal numbers that contain both positive and negative values. The format of the data has the decimal number first, followed by the sign, something like this:
123.45+
678.89-
I know I could reformat the data so the negative sign is first followed by the number and then I have no problems importing the data.
Question: Is there a way to import the data into SQL Server without reformating the data.
Gregory A. Larsen, MVP
September 22, 2004 at 1:56 pm
When you import the data apply a manual transform. Transforms use VBScript or JScript and you can fairly easily make a change like that as you are inserting into the table.
September 23, 2004 at 8:27 am
I am importing such values via DTS right now. Originating system is RM/COBOL on Unix, and can output signed numbers with either a leading or trailing sign value.
As Mr. Templeton suggested, trasnform script can easily handle. In essence, your source number comes to you in two DTSSource columns...first is number, second is sign.
Trick is to read the sign column, then take appropriate action. Note - there is no native function to switch sign in VBScript, so you need to create one (i.e., multiply by -1, for example).
Simple approach (where Col001 is number and Col002 is trailing sign value):
If DTSSource("Col002") = "+" Then DTSDestination("Signed_Number") = DTSSource(Col001")
If DTSSource("Col002") = "-" Then DTSDestination("Signed_Number") = SetNegativeFunction(DTSSource(Col001"))
September 23, 2004 at 12:07 pm
Thank you this works great.
Gregory A. Larsen, MVP
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply