display dates in a month

[cmuraz]

hi,
hope the query is simple, but i donno how to do.
i have some records in a table for the month may 2009 as
no date
---------------------------------------
1 02-may-09
4 10-may-09
13 12-may-09
20 24-may-09

only 4 days in may 09. but i want to display like
date no
-----------------------------------------
01-may-09 0
02-may-09 1
03-may-09 0
.
.
10-may-09 4
11-may-09 0
12-may-09 13
.
.
31-may-09 0

finally i need to display 31 days[all days ]in a month
how to do it in query. creating a view with this is needed for me.
pls help me..

thanks in advance..
muthu

[amitpatel66]

Try this:

Data in table1:

[code=oracle]

SQL> SELECT * FROM table1;

29-JUN-09 1
30-JUN-09 2
01-JUL-09 3
02-JUL-09 4

SQL> SELECT dt,no FROM
(SELECT dt,NVL(rn,0) no FROM
(select TRUNC(SYSDATE,' RRRR') + rn - 1 dt FROM
(SELECT rownum rn from all_objects where rownum <= DECODE(MOD(TO_C HAR(SYSDATE,'RR RR'),4),0,366,3 65))) x,
table1
WHERE TO_CHAR(x.dt,'D D-MON-RRRR') = (TO_CHAR(y.dat( +),'DD-MON-RRRR'))
AND TO_CHAR(dt,'MON-RRRR') IN (SELECT DISTINCT TO_CHAR(dat,'MO N-RRRR') FROM table1))
ORDER BY dt

[/code]