how to write INLINE SUM to this query [message #440330] |
Fri, 22 January 2010 07:59  |
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  |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
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
|
|
|