Post reply

Remove Trailing Zeros from Numeric(19,8) datatype

  • January 19, 2010 at 8:28 am

    #214770

    Hello,

    Here is my situation. I have a stored procedure that takes 2 columns from one table one column is numeric(19,8) and the other is numeric(19,6) multiplies them and then sticks them into a column in another table that has a datatype of numeric(19,8).

    My problem is an Arithmetic Overflow error because taking numbers like 10.6400000 and multiplying it by 10.640000 and trying to stick it into a numeric(19,8) column is not working.

    Basically I need to remove the trailing zeros from the original numeric columns so when the multiplication before the insert happens I don't get huge decimal places.

    Any ideas?

    Thanks.

  • January 19, 2010 at 8:35 am

    #1105467

    Yes, cast() it.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  • January 19, 2010 at 8:39 am

    #1105469

    Trailing digits would not cause an Arithmetic Overflow error. See example code below.

    create table #t ( MyNumber numeric(19,6) not null)

    insert into #t select 10.641111111111 * 10.64373737469083459

    select * from #t

    drop table #t

    Results:

    (1 row(s) affected)

    MyNumber

    ---------------------

    113.261192

    (1 row(s) affected)

  • January 19, 2010 at 8:39 am

    #1105470

    Try this:

    ROUND(Col1 * Col2, 8)

  • January 19, 2010 at 8:42 am

    #1105474

    Ahhh, I see. I just got the error on a select by itself. Thanks, I'll look into where else the issue could be coming from.

  • January 19, 2010 at 8:42 am

    #1105473

    I can't duplicate your problem. Can you provide table structures (DDL) and insert statements (DML) that illustrates the problem? (See the first link in my signature for how to do this.)

    I've tried:

    declare @test1 numeric(19,8)

    declare @Test2 numeric(19,6)

    declare @Test3 numeric(19,8)

    select @test1 = 10.64999999, @Test2 = 10.649999

    select @test1 * @Test2

    set @Test3 = @test1 * @Test2

    select @Test3

    declare @test-2 TABLE (Col1 numeric(19,8), Col2 numeric(19,6), Col3 numeric(19,8))

    insert into @test-2

    select @test1, @Test2, @test1 * @Test2

    select * from @test-2

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • January 19, 2010 at 8:55 am

    #1105485

    matt-996958 (1/19/2010)

    Ahhh, I see. I just got the error on a select by itself. Thanks, I'll look into where else the issue could be coming from.

    The only way I could get the numeric overflow error was when it went beyond the datatype:

    declare @test1 numeric(19,8)

    declare @Test2 numeric(19,6)

    declare @Test3 numeric(19,8)

    select @test1 = 11111111110.64999999, @Test2 = 10.649999

    select @test1 * @Test2

    set @Test3 = @test1 * @Test2At this point, @Test3 needs to be a numeric(20,8).

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • January 19, 2010 at 9:26 am

    #1105508

    Alright I have found that it isn't all of the data in the bulk insert after testing. What would be the best way to track down which records specifically it is failing to insert?

    Here is the insert statement being executed:

    INSERT INTO inventorycosts (storenum, applydate, trxnum, inventoryid, qty, costperbaseunit)

    SELECT h.storenum, h.applydate, h.vouchernum, d.itemnum,

    ISNULL(d.qty * CASE WHEN ISNULL(d.baseunitsperpurunit,0) <> 0 THEN d.baseunitsperpurunit ELSE 1 END, 0) qty,

    ISNULL(d.amount / CASE WHEN ISNULL(d.baseunitsperpurunit,0) <> 0 THEN d.baseunitsperpurunit ELSE 1 END, 0) costperbaseunit

    FROM dbo.invoiceheader h, dbo.invoicedetail d, dbo.inventoryheader ih, dbo.inventorydetail id

    WHERE h.vouchernum = d.vouchernum AND h.posted = 1 AND h.voided = 0 AND h.doctype = 1

    AND d.itemnum = ih.id AND ih.id = id.inventoryid AND h.storenum = id.storenum AND ISNULL(d.qty,0) <> 0

    AND (ih.prepitem = 0 OR id.calcprepitemcost = 0)

  • January 19, 2010 at 10:16 am

    #1105551

    How about?

    SELECT h.storenum, h.applydate, h.vouchernum, d.itemnum,

    ISNULL(d.qty * CASE WHEN ISNULL(d.baseunitsperpurunit,0) <> 0 THEN d.baseunitsperpurunit ELSE 1 END, 0) qty,

    ISNULL(d.amount / CASE WHEN ISNULL(d.baseunitsperpurunit,0) <> 0 THEN d.baseunitsperpurunit ELSE 1 END, 0) costperbaseunit

    FROM dbo.invoiceheader h, dbo.invoicedetail d, dbo.inventoryheader ih, dbo.inventorydetail id

    WHERE h.vouchernum = d.vouchernum AND h.posted = 1 AND h.voided = 0 AND h.doctype = 1

    AND d.itemnum = ih.id AND ih.id = id.inventoryid AND h.storenum = id.storenum AND ISNULL(d.qty,0) <> 0

    AND (ih.prepitem = 0 OR id.calcprepitemcost = 0)

    AND (Field1 * Field2) > 99999999999.99999999

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

Viewing 9 posts - 1 through 8 (of 8 total)

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