title: Exclude Duplicate Values in SUM with PeopleSoft Query
body: <p>Complete noob here, so bear with me!...</p>
  
  <p>I'm trying to write a query in <em>Oracle PeopleSoft</em> that is counting and summing an amount column of a table. The <code>COUNT</code> value works just fine; but because one of the tables joined in the query can have multiple values for each row of the table to which it is joined, the <code>SUM</code> is including multiple instances and giving overstated values. Here are the basics:</p>
  
  <p><strong>TABLE A:</strong> HEADER</p>
  
  <p><strong>TABLE B:</strong> LINE</p>
  
  <p><strong>TABLE C:</strong> ERROR_CODE (outer join to TABLE B)</p>
  
  <p>I am trying to sum the <code>B.LINE_AMOUNT</code> for every unique <code>A.HEADER_ENTRY</code>; but because each voucher line could have more than one <code>C.ERROR_CODE</code>, the query is multiplying the <code>B.LINE_AMOUNT</code> by each rule.</p>
  
  <p><strong>EXAMPLE</strong> -- <code>HEADER_ENTRY #12345</code> has 4 lines totaling <code>$50</code> each, and line 3 has 2 error codes. When I run the query, the result for that entry is <code>$250</code> instead of the <em>actual</em> <code>$200</code>.</p>
  
  <p><strong>Quick notes:</strong></p>
  
  <ul>
  <li><p>The actual query has 7 table records, and there are actually <em>two</em> different "error code" records (both of which are needed) -- both are on outer joins</p></li>
  <li><p>The <code>SUM</code> is conditional; so I'm currently using a <code>CASE WHEN</code> statement with it -><br><br> <code>SUM(CASE WHEN A.ENTRY_DATE &lt; :2 THEN B.LINE_AMOUNT ELSE 0 END)</code></p></li>
  </ul>
  
  <p>EDIT: As requested, here is the sql as taken from the PeopleSoft query:</p>
  
  <pre><code>SELECT A.BUSINESS_UNIT, CASE WHEN (':6' = 'Y') AND ( A.MATCH_STATUS_VCHR = 'M') THEN
  C.MATCH_RULE_ID
  WHEN (':6' = 'Y') AND ( A.VOUCHER_ID =  F.VOUCHER_ID) THEN
  F.MATCH_RULE_ID
  ELSE NULL END, A.ORIGIN, A.CLOSE_STATUS, A.MATCH_STATUS_VCHR, COUNT( DISTINCT  A.VOUCHER_ID), SUM( CASE WHEN ( TO_CHAR(E.JOURNAL_DATE,'YYYY-MM-DD') &lt; ':2') THEN  E.MONETARY_AMOUNT ELSE 0 END), TO_CHAR(SYSDATE,'YYYY-MM-DD')
    FROM PS_VOUCHER A, (PS_VOUCHER_LINE B LEFT OUTER JOIN  PS_MTCH_RULE_OVRD C ON  B.BUSINESS_UNIT = C.BUSINESS_UNIT AND B.VOUCHER_ID = C.VOUCHER_ID AND B.VOUCHER_LINE_NUM = C.VOUCHER_LINE_NUM ), ((PS_DISTRIB_LINE D LEFT OUTER JOIN  PS_VCHR_ACCTG_LINE E ON  D.BUSINESS_UNIT = E.BUSINESS_UNIT AND D.VOUCHER_ID = E.VOUCHER_ID AND D.VOUCHER_LINE_NUM = E.VOUCHER_LINE_NUM AND D.DISTRIB_LINE_NUM = E.DISTRIB_LINE_NUM AND E.LEDGER = D.LEDGER AND E.SHIPTO_ID &gt; ' ' ) LEFT OUTER JOIN  PS_AP_MTCH_EXCPTN F ON  E.BUSINESS_UNIT = F.BUSINESS_UNIT AND E.VOUCHER_ID = F.VOUCHER_ID AND E.VOUCHER_LINE_NUM = F.VOUCHER_LINE_NUM ), PS_VENDOR G
    WHERE A.DELETE_DT IS NULL
       AND ( A.ENTERED_DT BETWEEN CASE WHEN (TO_CHAR(TO_DATE(TO_DATE(:1,'YYYY-MM-DD')),'D') = 1) THEN
  TO_DATE(TO_DATE(:1,'YYYY-MM-DD'))-2
  WHEN (TO_CHAR(TO_DATE(TO_DATE(:1,'YYYY-MM-DD')),'D') = 2) THEN TO_DATE(TO_DATE(:1,'YYYY-MM-DD'))-3
  ELSE TO_DATE(TO_DATE(:1,'YYYY-MM-DD'))-1 END AND CASE WHEN (TO_CHAR(TO_DATE(TO_DATE(:2,'YYYY-MM-DD')),'D') = 1) THEN
  TO_DATE(TO_DATE(:2,'YYYY-MM-DD'))-2
  WHEN (TO_CHAR(TO_DATE(TO_DATE(:2,'YYYY-MM-DD')),'D') = 2) THEN TO_DATE(TO_DATE(:2,'YYYY-MM-DD'))-3
  ELSE TO_DATE(TO_DATE(:2,'YYYY-MM-DD'))-1 END
       OR ( A.MATCHED_DT &gt;= CASE WHEN (:9 = 'Y') THEN
  NULL
  WHEN ( A.CLOSE_STATUS = 'C') THEN
  TO_DATE(TO_DATE(:1,'YYYY-MM-DD'))-1
  ELSE TO_DATE(TO_DATE(:1,'YYYY-MM-DD')) END
       AND A.ENTERED_DT &lt;= CASE WHEN (TO_CHAR(TO_DATE(TO_DATE(:1,'YYYY-MM-DD')),'D') = 1) THEN
  TO_DATE(TO_DATE(:1,'YYYY-MM-DD'))-2
  WHEN (TO_CHAR(TO_DATE(TO_DATE(:1,'YYYY-MM-DD')),'D') = 2) THEN TO_DATE(TO_DATE(:1,'YYYY-MM-DD'))-3
  ELSE TO_DATE(TO_DATE(:1,'YYYY-MM-DD'))-1 END))
       AND A.MATCH_ACTION = 'Y'
       AND ( A.MATCHED_DT &gt; E.JOURNAL_DATE
       OR ( A.GROSS_AMT = 0
       AND A.MATCHED_DT &gt; A.ENTERED_DT)
       OR ( A.ACCOUNTING_DT &gt; A.ENTERED_DT
       AND A.MATCHED_DT &gt; A.MATCH_DUE_DT)
       OR B.RECEIPT_DT &gt; A.ENTERED_DT
       OR ( C.VOUCHER_ID IS NOT NULL
       AND A.MATCH_STATUS_VCHR IN ('C','M','O'))
       OR F.VOUCHER_ID IS NOT NULL
       OR A.MATCH_STATUS_VCHR NOT IN ('T','M','N'))
       AND ( A.MATCH_STATUS_VCHR = CASE WHEN (:3 = 'N') AND (:4 = 'N') AND (:5 = 'N') THEN
  A.MATCH_STATUS_VCHR
  WHEN (:3 = 'Y') THEN
  'M'
  ELSE NULL END
       OR C.MATCH_RULE_ID = CASE WHEN (:4 = 'Y') THEN
  C.MATCH_RULE_ID
  ELSE NULL END
       OR F.MATCH_RULE_ID = CASE WHEN (:4 = 'Y') THEN
  F.MATCH_RULE_ID
  ELSE NULL END)
       AND A.VENDOR_ID = CASE WHEN (:7 = ' ') THEN
  A.VENDOR_ID
  ELSE :7 END
       AND ( CASE WHEN (:6 = 'Y') AND ( A.MATCH_STATUS_VCHR = 'M') THEN
  C.MATCH_RULE_ID
  WHEN (:6 = 'Y') AND ( A.VOUCHER_ID =  F.VOUCHER_ID) THEN
  F.MATCH_RULE_ID
  ELSE NULL END = :8
       OR A.VOUCHER_ID = CASE WHEN (:8 = ' ') THEN
  A.VOUCHER_ID
  ELSE NULL END)
       AND A.BUSINESS_UNIT = B.BUSINESS_UNIT
       AND A.VOUCHER_ID = B.VOUCHER_ID
       AND B.BUSINESS_UNIT = D.BUSINESS_UNIT
       AND B.VOUCHER_ID = D.VOUCHER_ID
       AND B.VOUCHER_LINE_NUM = D.VOUCHER_LINE_NUM
       AND G.VENDOR_ID = A.VENDOR_ID
    GROUP BY  A.BUSINESS_UNIT,  CASE WHEN (':6' = 'Y') AND ( A.MATCH_STATUS_VCHR = 'M') THEN
  C.MATCH_RULE_ID
  WHEN (':6' = 'Y') AND ( A.VOUCHER_ID =  F.VOUCHER_ID) THEN
  F.MATCH_RULE_ID
  ELSE NULL END,  A.ORIGIN,  A.CLOSE_STATUS,  A.MATCH_STATUS_VCHR
    ORDER BY 1, 5, 2
  </code></pre>
  
  <p>When run, I get a perfect count; but the sum ends up being overstated because the "MTCH_RULE_OVRD" and "AP_MTCH_EXCPTN" tables can have multiple "MATCH_RULE_ID"s for each. Not sure if I need a query hint or what... but I'm STUCK-Overflow!</p>
  
  <p>I'm sure this is probably something really simple for you all! Thanks!</p>