Home » SQL & PL/SQL » SQL & PL/SQL » Row Source Generator (Oracle 9i,Windows XP)
| Row Source Generator [message #365209] |
Thu, 11 December 2008 02:14  |
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
|
Senior Member |
|
|
Hi Freinds,
Please Find The Sample Test Case.
--Create Table
CREATE TABLE Test
(
currdate DATE,
id NUMBER,
amount NUMBER
);
-- Insert Data
INSERT INTO TEST VALUES (TO_DATE('03/12/2008', 'dd/mm/yyyy'),1,400);
INSERT INTO TEST VALUES (TO_DATE('06/12/2008', 'dd/mm/yyyy'),1,200);
INSERT INTO TEST VALUES (TO_DATE('09/12/2008','dd/mm/yyyy'),1,800);
--Query Using Row Source Technique
SELECT
NVL(A.currdate,B.DateRange) DataDate,
A.ID,
A.AMOUNT
FROM
test A,
(SELECT
TO_DATE('12 2008','MM YYYY')-1 + LEVEL AS DateRange
FROM dual
WHERE (TO_DATE('12 2008','MM YYYY')-1+LEVEL)
<= LAST_DAY(TO_DATE('12 2008','MM YYYY'))
CONNECT BY LEVEL<=31) B
WHERE A.currdate(+)=B.DateRange
AND TO_CHAR(B.DateRange,'DD')<=
(SELECT TO_CHAR(TO_DATE('10-Dec-2008','DD-MON-YYYY'),'DD') FROM dual)
Output I Am Getting From Query:-
Row# DATADATE ID AMOUNT
1 2008/12/01 1 100
2 2008/12/02 {null} {null}
3 2008/12/03 1 400
4 2008/12/04 {null} {null}
5 2008/12/05 {null} {null}
6 2008/12/06 1 200
7 2008/12/07 {null} {null}
8 2008/12/08 {null} {null}
9 2008/12/09 1 800
10 2008/12/10 {null} {null}
Output I Like To get: -
Row# DATADATE ID AMOUNT
1 2008/12/01 1 100
2 2008/12/02 1 100
3 2008/12/03 1 400
4 2008/12/04 1 400
5 2008/12/05 1 400
6 2008/12/06 1 200
7 2008/12/07 1 200
8 2008/12/08 1 200
9 2008/12/09 1 800
10 2008/12/10 1 800
Regards,
Rajat Ratewal
|
|
|
|
|
|
| Re: Row Source Generator [message #365235 is a reply to message #365210] |
Thu, 11 December 2008 03:12  |
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
|
Senior Member |
|
|
Hi Michel,
Thanks for replying.Sorry i thought that the test case
can explain.But anyways here is the problem description.
As we are generating rows on date basis.
For date 2008/12/01 The amount is 100.
For date 2008/12/02 the date is missing and i
generated that date from row source technique.
Now what i want is to put the same amount and id for
date 2008/12/02 that is for 2008/12/01
When i reach next to 2008/12/03 data is available
but for for dates 2008/12/04,2008/12/05 i again generated
a row.
But i want same data as in 2008/12/03 into dates
2008/12/04,2008/12/05
Hope this clarifies.
Regards,
Rajat
|
|
|
|
| Re: Row Source Generator [message #365241 is a reply to message #365235] |
Thu, 11 December 2008 03:48 |
 |
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
| Quote: | thought that the test case an explain.
|
But why should we have to reverse engineer maybe wrongly your data and result when you can easily tell us what is the actual problem.
| Quote: | For date 2008/12/01 The amount is 100.
|
Why? There is nothing in the data you posted that infers that.
SQL> with
2 cal as (
3 select to_date('01/12/2008','DD/MM/YYYY')+level-1 dat
4 from dual connect by level <= 31
5 ),
6 data as (
7 select currdate, id, amount,
8 nvl(lead(currdate) over(order by currdate),
9 to_date('01/01/2009','DD/MM/YYYY')) nextdate
10 from test
11 union all
12 select to_date('01/12/2008','DD/MM/YYYY') currdate,
13 null, null, min(currdate)
14 from test
15 having min(currdate) > to_date('01/12/2008','DD/MM/YYYY')
16 )
17 select dat, nvl(id,0) id, nvl(amount,0) amount
18 from cal, data
19 where dat between currdate and nextdate-1
20 order by 1
21 /
DAT ID AMOUNT
----------- ---------- ----------
01-DEC-2008 0 0
02-DEC-2008 0 0
03-DEC-2008 1 400
04-DEC-2008 1 400
05-DEC-2008 1 400
06-DEC-2008 1 200
07-DEC-2008 1 200
08-DEC-2008 1 200
09-DEC-2008 1 800
10-DEC-2008 1 800
11-DEC-2008 1 800
12-DEC-2008 1 800
13-DEC-2008 1 800
14-DEC-2008 1 800
15-DEC-2008 1 800
16-DEC-2008 1 800
...
Regards
Michel
|
|
|
|
| Re: Row Source Generator [message #365248 is a reply to message #365241] |
Thu, 11 December 2008 04:04 |
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
|
Senior Member |
|
|
Thanks a lot it works.
| Quote: |
Why? There is nothing in the data you posted that infers that.
|
Sorry i missed this insert statement
INSERT INTO TEST VALUES (TO_DATE('01/12/2008', 'dd/mm/yyyy'),1,100);
Regards,
Rajat Ratewal
|
|
|
|
| Re: Row Source Generator [message #365280 is a reply to message #365248] |
Thu, 11 December 2008 05:17 |
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
|
Senior Member |
|
|
Further to add in this.
If i have duplicate records for a particular day.
Say for Date 03/12/2008 i add following row.
INSERT INTO TEST VALUES (TO_DATE('03/12/2008','dd/mm/yyyy'),1,600);
Now on 03/12/2008 data will be.
Row# CURRDATE ID AMOUNT
2 2008/12/03 1 400
3 2008/12/03 1 600
As we have generated rows for 04 and 05 dec.I would like
to generate 2 rows each for 04 and 05 Dec.
Output:-
Row# CURRDATE ID AMOUNT
2 2008/12/03 1 400
3 2008/12/03 1 600
4 2008/12/04 1 400
5 2008/12/04 1 600
6 2008/12/05 1 400
7 2008/12/05 1 600
Regards,
Rajat
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Feb 18 22:39:20 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
