| how to show non-existing data in reports [message #83355] |
Tue, 30 September 2003 05:00  |
Amit
Messages: 166
Registered: February 1999
|
Senior Member |
|
|
experts!
i have a typical problem.
in oracle8i,i have a table with date field.
in reports 6.0 , i want to display all the dates even if there is no record for the date.
e.g.
the existing data is
date count(*)
12-apr-03 10
13-apr-03 1
15-apr-03 101
17-apr-03 11
20-apr-03 2
i want report to show
date count(*)
12-apr-03 10
13-apr-03 1
14-apr-03 0
15-apr-03 11
16-apr-03 0
17-apr-03 11
18-apr-03 0
19-apr-03 0
20-apr-03 2
that is some thing urgent.
thanx in advance....
ams
|
|
|
|
| Re: how to show non-existing data in reports [message #83356 is a reply to message #83355] |
Tue, 30 September 2003 05:14  |
Amitabha
Messages: 14
Registered: May 2002
|
Junior Member |
|
|
THIS LOGIC CAN BE FURTHER MODIFIED TO SERVE YOUR PURPOSE. FETCH DATA FROM YOUR TABLE FOR THE DATE GENERATED LIKE SELECT NVL(DATA,0) FROM TABLE_NAME WHERE DATE=GENERATED_DATE,
1# IF REQUIRED YOU CAN USE INLINE VIEWS:
2# YOU CAN RUN A PROCEDURE TO POPULATE DATA IN A TEMP TABLE TO POPULATE REPORT DATA FIRST AND THEN RUN THE REPORT ON THAT TABLE
DECLARE
STD_DATE DATE :=TO_DATE('01-JAN-2003','DD/MM/YY');
BEGIN
WHILE 2 > 1 LOOP
DBMS_OUTPUT.PUT_LINE(TO_DATE(STD_DATE,'DD/MM/YY'));
STD_DATE:=STD_DATE+1;
EXIT WHEN STD_DATE=SYSDATE;
END LOOP;
END;
WRITE FOR MORE CLARIFICATION TO amitabhapalit@yahoo.com
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
