Home » SQL & PL/SQL » SQL & PL/SQL » how to write INLINE SUM to this query (oracle 10g)
how to write INLINE SUM to this query [message #440330] Fri, 22 January 2010 07:59 Go to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member


i want to write final SUM to the sub query LIKE (SELECT SUM(ALLOC_AMOUNT) (SELECT SUM(ALLOC_AMOUNT) * -1 )) so that i can compare ACTUAL_AMOUNT and INVOICE_AMT.
SELECT JC.CHARGE_ID CHARGEID, 
       JC.ACTUAL_AMOUNT * -1 ACTUAL_AMOUNT, 
       (SELECT SUM(ALLOC_AMOUNT) * -1 
          FROM DOC_CHARGE JC 
         WHERE JC.INVOICE_ID = JC.INVOICE_ID 
           AND MAIN_SEQ = 1 
        UNION 
        SELECT SUM(ALLOC_AMOUNT) * -1 
          FROM DOC_CHARGE_ABC ABC 
         WHERE ABC.INVOICE_ID = JC.INVOICE_ID 
        UNION 
        SELECT SUM(NET_AMOUNT) * -1 
          FROM INVOICE_ITEM ITEM 
         WHERE ITEM.INVOICE_ID = JC.INVOICE_ID) INVOICE_AMT 
  FROM DOC_CHARGE JC, JOBS JOB 
 WHERE JC.INVOICE_ID = '1234' 
   AND JOB.JOBID = JC.JOBID 
   AND JC.STATUS = 'INVOICED';
Re: how to write INLINE SUM to this query [message #440331 is a reply to message #440330] Fri, 22 January 2010 08:10 Go to previous message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do not use SELECT scalar views but FROM inline views.

Instead of
SELECT (SELECT SUM() FROM T1 WHERE ...),
       (SELECT SUM() FROM T2 WHERE ...)
FROM T3
WHERE ...
use
SELECT sum_t1, sum_t2
FROM T3,
     (SELECT something1, SUM() sum_t1 FROM T1 GROUP BY something1),
     (SELECT something2, SUM() sum_t2 FROM T2 GROUP BY something2)
WHERE ...
  AND something1 = something3
  AND something2 = maybeotherthing3

Regards
Michel
Previous Topic: Procedure type definition
Next Topic: Slow Query
Goto Forum:
  


Current Time: Wed Dec 07 03:24:34 CST 2016

Total time taken to generate the page: 0.05429 seconds