Post reply

Removing the scale part from an Oracle Number (p,s) datatype

  • July 8, 2012 at 3:29 am

    #392692

    Hi

    I have extracting data from a table in an Oracle 10g database using the Attunity for Oracle Source component.

    I have a field called account number which is displayed through my SQL Developer client like - 1234567890

    When I extract the data from this field and it gets loaded into a destination this account number appears like this - 1234567890.1

    I have checked what the datatype if for this field in the Oracle database and it's - Number (13,1) so SSIS and the Attunity for Oracle source component is working correctly I believe and it's probably just the NLS settings on SQL Developer client making the account number field appear without the decimal and 1 digit after it.

    I would like the decimal and 1 digit not to appear after the account number, any ideas?

    I have a workaround which is to convert the field to a string and then use the substring function to take the first 10 digits from the account number. I have already checked and every single value in the field is 10 digits long before the decimal and 1 digit. This is not ideal though as the number could grow over this length one day.

    The other idea have is to use the trunc function within the SQL query around the account number field in the Attunity for Oracle source component. I have yet to test this though.

    I do not want to use the round function because it may round up some of the account number values.

    So my aim is to have the account number appearing like this - 1234567890 and not this - 1234567890.1

    Thanks

  • July 9, 2012 at 3:33 am

    #1510228

    You can use the FLOOR function to get rid of the decimals.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • July 9, 2012 at 4:27 am

    #1510238

    Hi

    Thank you for your reply.

    I've only had an opportunity to test again today.

    I used the trunc function within the Attunity oracle source component in the end which worked.

    I am from an Oracle background so still getting used to some of the SQL Server functions but thanks for the FLOOR function suggestion. I am sure it will come in handy at some point again.

Viewing 3 posts - 1 through 2 (of 2 total)

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