Home » SQL & PL/SQL » SQL & PL/SQL » Need detail records but also need sum in same query
Need detail records but also need sum in same query [message #307732] Wed, 19 March 2008 13:53 Go to next message
bschmidt
Messages: 11
Registered: November 2007
Junior Member
Hi all, I love this forum!

I have a query that outputs detail records, but I also need to check the sum of those detail records for each document number and not output it if it sums to zero.

Ex. relevant data: document number, line id, amount

12345 1 300
12345 2 300
23456 1 -200
23456 2 200
34567 1 450

As I query the document numbers I need to check if the subtotal of the document's receipts is zero and not report it, but I still need to report each detail line where they do not sum to zero.

So my output on the report would be

12345 1 300
12345 2 300
34567 1 450

Right now, I run a query, then loop through each document number and run another query to check to see if the document subtotal is zero, then I do not add it to my report array. It's really slow, since I may run several hundred queries.

Here's my query...

SELECT DISTINCT trim(a.user_dm1) as ccadiv, trim(UPPER(SUBSTR(p.case_id, 8, 15 ))) as casenumber,
trim(p.prty_num) as partynumber, trim(v.nm) as partyname, trim(a.fund) as fund,
trim(substr(d.debt_typ_id, 8, 4)) as debttypid, trim(t.doc_typ_cd) as documenttype,
trim(t.doc_num) as documentnumber, trim(t.doc_ln_id) as lineid,
t.dllr_am as camt, t.doc_dt as receiptdate

FROM cca_dtla_tran_hist t, mf_ar_actg_dtl_hst a, cca_dbty_ln d, cca_prim_cc_acct p, mf_vend v

WHERE t.doc_dt >= TO_DATE('08-February-2008')
AND t.doc_dt <= TO_DATE('08-February-2008')
AND t.doc_ln_id = a.doc_line_id (+) AND trim(a.user_dm1) = 'TOP'
AND SUBSTR(t.parn_dbty_ln_id, 14, LENGTH(t.parn_dbty_ln_id)) = SUBSTR(d.uidy, 14, LENGTH(d.uidy))
AND d.parn_acct_id = p.uidy
AND t.vend_cd = v.cd

ORDER BY casenumber, partynumber, documentnumber, lineid, receiptdate

This reports all the detail as I need, but it also reports documents where the receipts total 0. When I use Group By and sum the amount I lose both detail lines for the output I want to print, since it combines them into one line.

Please help, thanks!
Re: Need detail records but also need sum in same query [message #307735 is a reply to message #307732] Wed, 19 March 2008 14:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is the third question you post, you should now know how to format it.
I remind you: OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

You can either use:
- a sub/inline query that returns the ids for which the sum is not 0
- SUM function in its analytical form and restrict to the lines for which this sum is not 0

By the way, "TO_DATE('08-February-2008')" may return an error depending on your environment:
SQL> select TO_DATE('08-February-2008') from dual;

ORA-01858: a non-numeric character was found where a numeric was expected 

Regards
Michel

Re: Need detail records but also need sum in same query [message #307740 is a reply to message #307732] Wed, 19 March 2008 14:47 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
Here are some similar examples, where I get detail records from the emp table where the sum of the sal for that deptno is > 9000, which you can change to > 0 in your query. These are just examples. There are various other ways.

SCOTT@orcl_11g> SELECT e.deptno, e.empno, sal,
  2  	    (SELECT SUM (SAL)
  3  	     FROM   emp
  4  	     WHERE  deptno = e.deptno) AS total
  5  FROM   emp e
  6  ORDER  BY deptno, empno
  7  /

    DEPTNO      EMPNO        SAL      TOTAL
---------- ---------- ---------- ----------
        10       7782       2450       8750
        10       7839       5000       8750
        10       7934       1300       8750
        20       7369        800      10875
        20       7566       2975      10875
        20       7788       3000      10875
        20       7876       1100      10875
        20       7902       3000      10875
        30       7499       1600       9400
        30       7521       1250       9400
        30       7654       1250       9400
        30       7698       2850       9400
        30       7844       1500       9400
        30       7900        950       9400

14 rows selected.

SCOTT@orcl_11g> SELECT deptno, empno, sal
  2  FROM   (SELECT e.deptno, e.empno, sal,
  3  		    (SELECT SUM (SAL)
  4  		     FROM   emp
  5  		     WHERE  deptno = e.deptno) AS total
  6  	     FROM   emp e)
  7  WHERE  total > 9000
  8  ORDER  BY deptno, empno
  9  /

    DEPTNO      EMPNO        SAL
---------- ---------- ----------
        20       7369        800
        20       7566       2975
        20       7788       3000
        20       7876       1100
        20       7902       3000
        30       7499       1600
        30       7521       1250
        30       7654       1250
        30       7698       2850
        30       7844       1500
        30       7900        950

11 rows selected.

SCOTT@orcl_11g> SELECT e.deptno, e.empno, sal
  2  FROM   emp e,
  3  	    (SELECT deptno, SUM (sal) AS total
  4  	     FROM   emp
  5  	     GROUP  BY deptno) t
  6  WHERE  e.deptno = t.deptno
  7  AND    t.total > 9000
  8  ORDER  BY deptno, empno
  9  /

    DEPTNO      EMPNO        SAL
---------- ---------- ----------
        20       7369        800
        20       7566       2975
        20       7788       3000
        20       7876       1100
        20       7902       3000
        30       7499       1600
        30       7521       1250
        30       7654       1250
        30       7698       2850
        30       7844       1500
        30       7900        950

11 rows selected.

SCOTT@orcl_11g> 

Re: Need detail records but also need sum in same query [message #307742 is a reply to message #307740] Wed, 19 March 2008 14:53 Go to previous message
bschmidt
Messages: 11
Registered: November 2007
Junior Member
Thank you for the quick responses, I will try your example, and format correctly in the future.
Previous Topic: MAX function
Next Topic: how to know any table is being used by any mviews
Goto Forum:
  


Current Time: Fri Dec 09 09:42:21 CST 2016

Total time taken to generate the page: 0.11908 seconds