1 Reply Latest reply on Nov 28, 2016 11:59 AM by Sarah Ebreo

    Wrong SUM result when INNER JOIN multiplied row of records

    Thai Huynh

      BACKGROUND:

       

      I map defects using background image and polygons.

      For clarity, I am showing you the defect records found for one of the polygons

       

      NC_KEYLEFT_RIGHT
      LOCATION
      Def_QTY
      N1490364542LSECTION_431
      N1490369230LSECTION_431
      N1490329493LSECTION_431
      N14903978680LSECTION_430
      N14903750lLSECTION_431
      N1490382545LSECTION_431
      N1490393759LSECTION_431
      N1490391497LSECTION_431
      N1490405688LSECTION_431
      N1490398211LSECTION_431
      N1490378372LSECTION_431
      N1490370496LSECTION_431
      N1490396900LSECTION_431
      N2780060835LSECTION_435

       

      When I inner join these records with the table of Path ID, X, Y coordinates in order to plot the polygons, each defect record above is multiplied by the number of path ID - as expected by the INNER JOIN.

      Here are the path IDs for one of the polygons in question

      LEFT_RIGHTLOCATIONPath_IDX
      Y
      LSECTION_431230115
      LSECTION_432655115
      LSECTION_43365557
      LSECTION_43462757
      LSECTION_43560858
      LSECTION_43658560
      LSECTION_43725959
      LSECTION_43822960

       

      PROBLEM:

      When I inner join the two data tables I get 112 rows (14 data rows x 8 path IDs). The expected defect qty for SECTION_43 polygon is 17 ( the sum of the Def Qty column).

      But because of the multiplied number of records, I get 136. That is, each NC_KEY's quantity is "duplicated" 8 times and then summed up.

       

      I tried Level of Detail (LOD) calculation and nested LOD but can't get the expected result.

       

      Can you help please? Thank you.