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 Go to next message
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 #365210 is a reply to message #365209] Thu, 11 December 2008 02:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Output I Like To get: -

Which is in words?

Regards
Michel
Re: Row Source Generator [message #365235 is a reply to message #365210] Thu, 11 December 2008 03:12 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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

Re: Row Source Generator [message #365284 is a reply to message #365280] Thu, 11 December 2008 05:23 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What did you try to improve my query in order to fit your new requirements?

Regards
Michel
Previous Topic: Regarding Grouping of data
Next Topic: Comma Separated Number
Goto Forum:
  


Current Time: Sat Dec 10 16:48:57 CST 2016

Total time taken to generate the page: 0.04529 seconds