Post reply

parentheses effecting the scale

  • January 12, 2018 at 8:26 am

    #342366

    In the code below, the placement of the parentheses is effecting the result of mktVal1.  Can someone please explain why?


    declare @MktVal1 decimal(35,15) = 0
    declare @ShsTD1 decimal(35,15) = 1000000.0000
    declare @PxDirty1 decimal(35,15) = 107.79502
    declare @OptFactor1 decimal(35,15) = .01
    declare @PxFx1 decimal(35,15) = 1.35804

    set @mktVal1 = @ShsTD1 * (@PxDirty1 * @OptFactor1 * @PxFx1)

    SELECT @mktVal1

    set @mktVal1 = (@ShsTD1 * @PxDirty1 * @OptFactor1) * @PxFx1

    SELECT @mktVal1

  • January 12, 2018 at 8:38 am

    #1975724

    Nevermind.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • January 12, 2018 at 8:56 am

    #1975734

    Glad you solved it.

  • January 12, 2018 at 9:02 am

    #1975736

    Steve Jones - SSC Editor - Friday, January 12, 2018 8:56 AM

    Glad you solved it.

    Not sure the OP has, Steve. That was me who'd realised I'd made an incorrect assumption, and I can't delete my own posts. 🙂

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • January 12, 2018 at 9:17 am

    #1975744

    No, I haven't solved it 🙁

  • January 12, 2018 at 9:31 am

    #1975748

    You've got some sort of rounding error there.  Does this help to explain it?

    John

  • January 12, 2018 at 10:04 am

    #1975750

    Yeah, since you're dealing with such high precisions and scales in the multiplied types, each multiplication will just end up yielding a DECIMAL (38,6) per the third rule for multiplication and division listed in the MS doc.

    Since the result of the three multiplications in parentheses for the first calculation will be rounded to fit in a scale of 6 and @ShsTD1 is 10^6, you'll just end up with a nice whole number.

    In the second calculation you just have a different result rounded to fit in a scale of 6, and a different final result as a consequence. 

    It shows one of the dangers of using data types bigger than you need. If you use "just right" data types for each number, then the outputs will match and no rounding will occur (all the datatypes' precisions plus 1 for each multiplication is still well below 38).

    declare @MktVal1 decimal(35,15) = 0
    declare @ShsTD1 decimal(11,4)= 1000000.0000
    declare @PxDirty1 decimal(8,5) = 107.79502
    declare @OptFactor1 decimal(2,2) = .01
    declare @PxFx1 decimal(6,5) = 1.35804

    set @mktVal1 = @ShsTD1 *  (@PxDirty1 * @OptFactor1 * @PxFx1)

    SELECT @mktVal1

    set @mktVal1 = (@ShsTD1 * @PxDirty1 * @OptFactor1) * @PxFx1

    SELECT @mktVal1

    Cheers!

  • January 12, 2018 at 10:14 am

    #1975752

    Thanks Jacob.  Along with John's link and your explanation I understand why there is a difference between the two results.

    Thanks again Guys!

  • January 12, 2018 at 10:16 am

    #1975754

    Looks like it comes down to how things are being rounded during the computation.  You can start by looking at the various pieces.

  • January 12, 2018 at 12:30 pm

    #1975767

    Just for the heck of it I made some changes to the data type definitions and interestingly enough both queries now return the same result.

    declare @MktVal1 decimal(35,10) = 0
    declare @ShsTD1 decimal(11,4) = 1000000.0000
    declare @PxDirty1 decimal(9,5) = 107.79502
    declare @OptFactor1 decimal(2,2) = .01
    declare @PxFx1 decimal(6,5) = 1.35804

    --set @mktVal1 = @ShsTD1 * (@PxDirty1 * @OptFactor1 * @PxFx1)

    SELECT @ShsTD1 * (@PxDirty1 * @OptFactor1 * @PxFx1), (@PxDirty1 * @OptFactor1 * @PxFx1), @ShsTD1

    --SELECT @mktVal1

    --set @mktVal1 = (@ShsTD1 * @PxDirty1 * @OptFactor1) * @PxFx1

    SELECT (@ShsTD1 * @PxDirty1 * @OptFactor1) * @PxFx1, (@ShsTD1 * @PxDirty1 * @OptFactor1), @PxFx1

    --SELECT @mktVal1
    GO

  • January 12, 2018 at 12:34 pm

    #1975768

    Lynn Pettis - Friday, January 12, 2018 12:30 PM

    Just for the heck of it I made some changes to the data type definitions and interestingly enough both queries now return the same result.

    declare @MktVal1 decimal(35,10) = 0
    declare @ShsTD1 decimal(11,4) = 1000000.0000
    declare @PxDirty1 decimal(9,5) = 107.79502
    declare @OptFactor1 decimal(2,2) = .01
    declare @PxFx1 decimal(6,5) = 1.35804

    --set @mktVal1 = @ShsTD1 * (@PxDirty1 * @OptFactor1 * @PxFx1)

    SELECT @ShsTD1 * (@PxDirty1 * @OptFactor1 * @PxFx1), (@PxDirty1 * @OptFactor1 * @PxFx1), @ShsTD1

    --SELECT @mktVal1

    --set @mktVal1 = (@ShsTD1 * @PxDirty1 * @OptFactor1) * @PxFx1

    SELECT (@ShsTD1 * @PxDirty1 * @OptFactor1) * @PxFx1, (@ShsTD1 * @PxDirty1 * @OptFactor1), @PxFx1

    --SELECT @mktVal1
    GO

    I can't help but strongly approve the spirit of this example 😛

  • January 12, 2018 at 12:37 pm

    #1975770

    Jacob Wilkins - Friday, January 12, 2018 12:34 PM

    Lynn Pettis - Friday, January 12, 2018 12:30 PM

    Just for the heck of it I made some changes to the data type definitions and interestingly enough both queries now return the same result.

    declare @MktVal1 decimal(35,10) = 0
    declare @ShsTD1 decimal(11,4) = 1000000.0000
    declare @PxDirty1 decimal(9,5) = 107.79502
    declare @OptFactor1 decimal(2,2) = .01
    declare @PxFx1 decimal(6,5) = 1.35804

    --set @mktVal1 = @ShsTD1 * (@PxDirty1 * @OptFactor1 * @PxFx1)

    SELECT @ShsTD1 * (@PxDirty1 * @OptFactor1 * @PxFx1), (@PxDirty1 * @OptFactor1 * @PxFx1), @ShsTD1

    --SELECT @mktVal1

    --set @mktVal1 = (@ShsTD1 * @PxDirty1 * @OptFactor1) * @PxFx1

    SELECT (@ShsTD1 * @PxDirty1 * @OptFactor1) * @PxFx1, (@ShsTD1 * @PxDirty1 * @OptFactor1), @PxFx1

    --SELECT @mktVal1
    GO

    I can't help but strongly approve the spirit of this example 😛

    I didn't even see your post.  I had taken the original post and just played with it a bit.  Great minds think a like. 😀

  • January 12, 2018 at 2:12 pm

    #1975787

    Very cool.  Thanks Lynn 🙂

Viewing 13 posts - 1 through 12 (of 12 total)

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