ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

SUMMING Fields with Division, Drops Decimals

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • SUMMING Fields with Division, Drops Decimals

    I notice the more fields I add together , then divide the result, the fewer decimal positions I get in my result.

    For instance, the first result has 12 decimals

    What's going on ? I want 2 positions of decimals for the last select where I add 12 fields together. It's dropping them and just showing 17.00

    Code:
    SELECT
     ((SFDL01 + SFDL02) / SADLCY ) * 100                 [COLOR=#FF0000]Result = 4.678465299100[/COLOR]
    , ((SFDL01 + SFDL02+ SFDL03) / SADLCY ) * 100
    , ((SFDL01 + SFDL02+ SFDL03+ SFDL04 ) / SADLCY ) * 100
    , ((SFDL01 + SFDL02+ SFDL03+ SFDL04+ SFDL05 ) / SADLCY ) * 100
    , ((SFDL01 + SFDL02+ SFDL03+ SFDL04+ SFDL05+ SFDL06 ) / SADLCY ) * 100
    , ((SFDL01 + SFDL02+ SFDL03+ SFDL04+ SFDL05+ SFDL06+ SFDL07 ) / SADLCY ) * 100     [COLOR=#FF0000]Result = 10.2363300[/COLOR]
    , ((SFDL01 + SFDL02+ SFDL03+ SFDL04+ SFDL05+ SFDL06+ SFDL07+ SFDL08  ) / SADLCY ) * 100
    , ((SFDL01 + SFDL02+ SFDL03+ SFDL04+ SFDL05+ SFDL06+ SFDL07+ SFDL08 + SFDL09 ) / SADLCY ) * 100
    , ((SFDL01 + SFDL02+ SFDL03+ SFDL04+ SFDL05+ SFDL06+ SFDL07+ SFDL08 + SFDL09+ SFDL10 ) / SADLCY ) * 100
    , ((SFDL01 + SFDL02+ SFDL03+ SFDL04+ SFDL05+ SFDL06+ SFDL07+ SFDL08 + SFDL09+ SFDL10+ SFDL11) / SADLCY ) * 100
    , ((SFDL01 + SFDL02+ SFDL03+ SFDL04+ SFDL05+ SFDL06+ SFDL07+ SFDL08 + SFDL09+ SFDL10+ SFDL11+ SFDL12 ) / SADLCY ) * 100    [COLOR=#FF0000] Result = 17.00 [/COLOR]
    FROM TABLE_A
    If I try to CAST as 2 decimals, they still get dropped.
    I want 38.46, not 38.00

    Code:
    CAST( ((SFDL01 + SFDL02+ SFDL03+ SFDL04+ SFDL05+ SFDL06+ SFDL07+ SFDL08 + SFDL09+ SFDL10 ) / SADLCY ) * 100 as dec(10, 2))  [COLOR=#FF0000]Result = 38.46[/COLOR]
    ,CAST(  ((SFDL01 + SFDL02+ SFDL03+ SFDL04+ SFDL05+ SFDL06+ SFDL07+ SFDL08 + SFDL09+ SFDL10+ SFDL11) / SADLCY ) * 100 as dec(10, 2))   [COLOR=#FF0000]Result = 38.40[/COLOR]
    ,CAST(  ((SFDL01 + SFDL02+ SFDL03+ SFDL04+ SFDL05+ SFDL06+ SFDL07+ SFDL08 + SFDL09+ SFDL10+ SFDL11+ SFDL12 ) / SADLCY ) * 100 as dec(10, 2))  [COLOR=#FF0000]Result = 38.00[/COLOR]
    FROM TABLE_A
    Last edited by MFisher; May 13, 2020, 12:34 PM.

  • #2
    I think you need to move the cast to just the values being added together
    Code:
    ( (cast(SFDL01 + SFDL02 as dec(something,something))) / SADLCY ) * 100
    This page explains how SQL determines the precision and scale of intermediate results: https://www.ibm.com/support/knowledg...arithmetic.htm

    I don't know what are the right values to cast to. It depends on the precision and scale of your variables.

    MFisher, for the other folks in this forum who might have time to spend more time on this, it would be useful if you told us the definitions of your fields, and the values.

    Comment


    • #3
      Fields:
      Code:
      Field                  
      Name     Type Length  Dec
      
      SFDL01     P     18    5
      SFDL02     P     18    5
      SFDL03     P     18    5
      SFDL04     P     18    5
      SFDL05     P     18    5
      SFDL06     P     18    5
      SFDL07     P     18    5
      SFDL08     P     18    5
      Some sample Data

      Click image for larger version  Name:	SALAF_Sample.png Views:	0 Size:	15.4 KB ID:	152972
      Last edited by MFisher; May 13, 2020, 05:08 PM.

      Comment


      • #4
        Originally posted by Barbara Morris View Post
        I think you need to move the cast to just the values being added together
        Code:
        ( (cast(SFDL01 + SFDL02 as dec(something,something))) / SADLCY ) * 100
        This page explains how SQL determines the precision and scale of intermediate results: https://www.ibm.com/support/knowledg...arithmetic.htm

        I don't know what are the right values to cast to. It depends on the precision and scale of your variables.

        MFisher, for the other folks in this forum who might have time to spend more time on this, it would be useful if you told us the definitions of your fields, and the values.
        Thanks !... I added an Inner CAST, that worked !
        Last edited by MFisher; May 15, 2020, 09:23 AM.

        Comment

        Working...
        X