Home » SQL & PL/SQL » SQL & PL/SQL » Why don't I get a number in the first row?
Why don't I get a number in the first row? [message #193643] Mon, 18 September 2006 12:54 Go to next message
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 Go to previous message
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.
Previous Topic: Converting a SQL Server stored procedure to Oracle
Next Topic: what is the difference between 8i and 9i
Goto Forum:
  


Current Time: Sat Dec 03 09:47:14 CST 2016

Total time taken to generate the page: 0.10419 seconds