| Why don't I get a number in the first row? [message #193643] |
Mon, 18 September 2006 12:54  |
Matthew Waugh
Messages: 71
Registered: October 2004
|
Member |
|
|
The field should Sum up the gross values of prior checks, and then add the gross from the current check. It works for all rows except the first. What gives? Is the subquery returning a NULL value, to which you can't add a number?
PAY FILE_N CHECK_DT CHECK_NBR GROSS ACC_GROSS
--- ------ --------- ---------- ---------- ----------
QAX 018879 24-AUG-06 2472 3795
QAX 018879 31-AUG-06 1 108.28 3903.28
QAX 018879 31-AUG-06 6 162.88 4066.16
QAX 018879 07-SEP-06 0 148.48 4214.64
SELECT C.PAYGROUP,C.FILE_NBR,C.CHECK_DT,
C.CHECK_NBR,SUM(C.CK_GROSS) GROSS,
(
(SELECT SUM(C2.CK_GROSS)
FROM PS_AL_CHK_DATA C2
WHERE C2.PAYGROUP = C.PAYGROUP
AND C2.FILE_NBR = C.FILE_NBR
AND (
(C2.CHECK_DT < C.CHECK_DT)
OR
(C2.CHECK_DT = C.CHECK_DT AND
C2.CHECK_NBR < C.CHECK_NBR)
)
) + SUM(C.CK_GROSS)
) ACC_GROSS
FROM PS_AL_CHK_DATA C
WHERE C.PAYGROUP = 'QAX'
AND C.ENTRY_NBR > 0
AND C.FILE_NBR = '018879'
GROUP BY C.PAYGROUP,C.FILE_NBR,
C.CHECK_NBR,C.CHECK_DT
ORDER BY CHECK_DT,CHECK_NBR
|
|
|
|
| Re: Why don't I get a number in the first row? [message #193644 is a reply to message #193643] |
Mon, 18 September 2006 13:08  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Yes, adding numbers to a NULL will result in a NULL. You could use NVL to convert the NULL to a 0 (zero) though.
Have you considered using the SUM analytic function to simply that running sum?
sql>select deptno, sum_sal, sum(sum_sal) over (order by deptno) accum_sal
2 from (select deptno, sum(sal) sum_sal
3 from emp
4 group by deptno);
DEPTNO SUM_SAL ACCUM_SAL
--------- --------- ---------
10 8750 8750
20 10875 19625
30 9400 29025
3 rows selected. |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
