Home » SQL & PL/SQL » SQL & PL/SQL » Decode, NVL or which do I use?
icon10.gif  Decode, NVL or which do I use? [message #184205] Tue, 25 July 2006 13:27 Go to next message
melsterDBA
Messages: 1
Registered: July 2006
Junior Member
I have the following query:


SELECT TO_CHAR(reconcile_date,'yyyymm'),
COUNT(*),
200407
FROM TEMP_MMODRPT tg,
(SELECT per_id,
start_date,
MIN(reconcile_date)
FROM TEMP_MMODRPT
WHERE reconcile_date IS NOT NULL
AND billing_period = 'A'
GROUP BY per_id,
start_date
HAVING (MIN(reconcile_date) >= TO_DATE ('08/01/2004','mm/dd/yyyy')
AND MIN(reconcile_date)< TO_DATE('09/01/2004','mm/dd/yyyy'))
ORDER BY per_id, start_date) tm
WHERE tg.per_id = tm.per_id
AND tg.start_date = tm.start_date
AND tg.billing_period = 'A'
AND (reconcile_date >= TO_DATE('08/01/2004','mm/dd/yyyy')
AND reconcile_date < TO_DATE('07/01/2006','mm/dd/yyyy')
)
GROUP BY TO_CHAR(reconcile_date,'yyyymm');

The results look like this where the 1st col is a date, 2nd col is a COUNT(*), 3rd col is hardcoded header.

TO_CHA COUNT(*) 200407
------ ---------- ----------
200408 7593 200407
200508 4721 200407
200509 3 200407
200510 1 200407
200511 6 200407
200602 5 200407
200604 1 200407
200605 1 200407


I would like the results to look more like this, where if I have no record for the date, then put a count of 0 like so:

TO_CHA COUNT(*) 200407
------ ---------- ----------
200408 7593 200407
200409 0 200407
200410 0 200407
200411 0 200407
200412 0 200407
200501 0 200407
200502 0 200407
200503 0 200407
200504 0 200407
200505 0 200407
200506 0 200407
200507 0 200407
200508 4721 200407
200509 3 200407
200510 1 200407
200511 6 200407
200512 0 200407
200601 0 200407
200602 5 200407
200603 0 200407
200604 1 200407
200605 1 200407

Basically, fill in a record of "0" for the date with no recs.
Can you please help with this query?

Thanks!
Re: Decode, NVL or which do I use? [message #184226 is a reply to message #184205] Tue, 25 July 2006 15:21 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
SELECT TO_CHAR(TG.reconcile_date,'yyyymm'),
NVL(COUNT(*),0),
200407
FROM TEMP_MMODRPT tg,
(SELECT per_id,
start_date,
MIN(reconcile_date)
FROM TEMP_MMODRPT
WHERE reconcile_date IS NOT NULL
AND billing_period = 'A'
GROUP BY per_id,
start_date
HAVING (MIN(reconcile_date) >= TO_DATE ('08/01/2004','mm/dd/yyyy')
AND MIN(reconcile_date)< TO_DATE('09/01/2004','mm/dd/yyyy'))
ORDER BY per_id, start_date) tm
WHERE tg.per_id = tm.per_id(+)
AND tg.start_date = tm.start_date(+)
AND tg.billing_period = 'A'
AND (reconcile_date >= TO_DATE('08/01/2004','mm/dd/yyyy')
AND reconcile_date < TO_DATE('07/01/2006','mm/dd/yyyy')
)
GROUP BY TO_CHAR(reconcile_date,'yyyymm');
Previous Topic: variable from shell to sqlplus
Next Topic: Joins
Goto Forum:
  


Current Time: Sun Dec 11 04:34:07 CST 2016

Total time taken to generate the page: 0.07284 seconds