Home » SQL & PL/SQL » SQL & PL/SQL » Replicate Rows (10.2.0.4.0)
Replicate Rows [message #422540] Thu, 17 September 2009 06:10 Go to next message
maheshmhs
Messages: 93
Registered: October 2008
Location: Nepal
Member
Hi,

I have a table which also contains 2 columns startdate and enddate. My requirement is that for each rows i need to look at its startdate and enddate and modify these fields. for eg if start date is 20040101 and enddate is 20070624
ie
(.......startdate.......enddate)
.......20040101.......20070624
the row should be replicated as

.......20040101.......20041231
.......20050101.......20051231
.......20060101.......20061231
.......20070101.......20070624

here .represents other columns

Here a single row has been replicated to 4 rows with everything same except startdate and enddate.
I am clueless as how to perform it.

Any Suggestions are highly appreciated.

thank youi
Re: Replicate Rows [message #422543 is a reply to message #422540] Thu, 17 September 2009 06:27 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Don't know exactly how to do, but, I think, MODEL clause can help you.

regards,
Delna
Re: Replicate Rows [message #422544 is a reply to message #422540] Thu, 17 September 2009 06:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a working Test case: create table and insert statements along with the result you want with these data.

Search for "row generator".

Regards
Michel
Re: Replicate Rows [message #422551 is a reply to message #422544] Thu, 17 September 2009 06:54 Go to previous messageGo to next message
maheshmhs
Messages: 93
Registered: October 2008
Location: Nepal
Member
Thank You Michel Cadot

The working test case would be
CREATE TABLE zzz_demo
(
member VARCHAR2(50),
startdate DATE,
enddate DATE ,
country VARCHAR2(10)
);

INSERT INTO zzz_demo 
VALUES ('123',To_Date(20040101,'YYYYMMDD'),To_Date(20060301,'YYYYMMDD'),'nepal')
INSERT INTO zzz_demo 
VALUES ('245',To_Date(20050101,'YYYYMMDD'),To_Date(20060301,'YYYYMMDD'),'india')
COMMIT;

Now the table looks like
SELECT * FROM zzz_demo;

MEMBER STARTDATE           ENDDATE             COUNTRY
123    01.01.2004 00:00:00 01.03.2006 00:00:00 nepal  
245    01.01.2005 00:00:00 01.03.2006 00:00:00 india 


and the output should be like
123    01.01.2004 00:00:00 32.12.2004 00:00:00 nepal
123    01.01.2005 00:00:00 32.12.2005 00:00:00 nepal
123    01.01.2006 00:00:00 01.03.2006 00:00:00 nepal
245    01.01.2005 00:00:00 32.12.2005 00:00:00 india
245    01.01.2006 00:00:00 01.03.2006 00:00:00 india


thank you
Re: Replicate Rows [message #422553 is a reply to message #422551] Thu, 17 September 2009 07:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here's a clue:
SQL> with dates as (select sysdate-1000 fromdate, sysdate todate from dual)
  2  select extract(year from fromdate)+level-1 YEAR
  3  from dates
  4  connect by level <= extract(year from todate)-extract(year from fromdate)+1
  5  /
      YEAR
----------
      2006
      2007
      2008
      2009

4 rows selected.

Regards
Michel
Re: Replicate Rows [message #422624 is a reply to message #422553] Thu, 17 September 2009 23:53 Go to previous messageGo to next message
maheshmhs
Messages: 93
Registered: October 2008
Location: Nepal
Member
it worked.

thanks a million!
Re: Replicate Rows [message #422635 is a reply to message #422624] Fri, 18 September 2009 00:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please post the final query following the forum guide.

Forum Guide, Posting guidelines section
If you found an answer yourself, post it. That way we know the issue is resolved and we might learn from it.

And please, post it formatted.

Regards
Michel

[Updated on: Fri, 18 September 2009 00:40]

Report message to a moderator

Re: Replicate Rows [message #422658 is a reply to message #422635] Fri, 18 September 2009 03:11 Go to previous messageGo to next message
maheshmhs
Messages: 93
Registered: October 2008
Location: Nepal
Member
Initially the table contained..
SELECT * FROM zzz_demo;
MEMBER STARTDATE           ENDDATE             COUNTRY
123    01.01.2004 00:00:00 01.03.2006 00:00:00 nepal  
245    01.01.2005 00:00:00 01.03.2006 00:00:00 india  
423    23.04.2004 00:00:00 12.09.2005 00:00:00 china  
423    14.09.2005 00:00:00 14.08.2006 00:00:00 china  
423    14.07.2006 00:00:00 14.08.2007 00:00:00 china  
678    14.07.2006 00:00:00                     bangla 
678                        14.07.2006 00:00:00 lanka  

Then following query was used,
select  DISTINCT member,
CASE WHEN (EXTRACT(YEAR FROM STARTDATE)+ LEVEL -1)<=
   EXTRACT(YEAR FROM STARTDATE) THEN STARTDATE ELSE
   To_Date((EXTRACT(YEAR FROM Nvl(STARTDATE,ENDDATE))+ 
   LEVEL-1)||'0101','YYYYMMDD') END virtualStart,
CASE WHEN (EXTRACT(YEAR FROM Nvl(STARTDATE,ENDDATE))+ 
   LEVEL -1)>=EXTRACT(YEAR FROM ENDDATE) THEN ENDDATE ELSE
   To_Date((EXTRACT(YEAR FROM Nvl(STARTDATE,ENDDATE))+ 
   LEVEL -1)||'1231','YYYYMMDD') END virtualEnd,
country
FROM zzz_demo
connect by level <= extract(year from ENDDATE)-
extract(year from STARTDATE)+1

Then i got the following result.
MEMBER VIRTUALSTART        VIRTUALEND          COUNTRY
123    01.01.2004 00:00:00 31.12.2004 00:00:00 nepal  
123    01.01.2005 00:00:00 31.12.2005 00:00:00 nepal  
123    01.01.2006 00:00:00 01.03.2006 00:00:00 nepal  
245    01.01.2005 00:00:00 31.12.2005 00:00:00 india  
245    01.01.2006 00:00:00 01.03.2006 00:00:00 india  
423    23.04.2004 00:00:00 31.12.2004 00:00:00 china  
423    01.01.2005 00:00:00 12.09.2005 00:00:00 china  
423    14.09.2005 00:00:00 31.12.2005 00:00:00 china  
423    01.01.2006 00:00:00 14.08.2006 00:00:00 china  
423    14.07.2006 00:00:00 31.12.2006 00:00:00 china  
423    01.01.2007 00:00:00 14.08.2007 00:00:00 china  
678    01.01.2006 00:00:00 14.07.2006 00:00:00 lanka  
678    14.07.2006 00:00:00 31.12.2006 00:00:00 bangla 


This is what i had required.
The only problem is that this query is really very slow for larger tables.

Thanks.

Re: Replicate Rows [message #422662 is a reply to message #422658] Fri, 18 September 2009 03:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select * from  zzz_demo ;
MEMBER STARTDATE  ENDDATE    COUNTRY
------ ---------- ---------- ----------
123    01/01/2004 01/03/2006 nepal
245    01/01/2005 01/03/2006 india

2 rows selected.

SQL> with years as (select 12*(level-1) yearnb from dual connect by level <= 10)
  2  select member,
  3         greatest(startdate,add_months(trunc(startdate,'YEAR'),yearnb)) startdate,
  4         least(enddate,add_months(trunc(startdate,'YEAR'),yearnb+12)-1) enddate,
  5         country
  6  from zzz_demo, years
  7  where yearnb <= 12*(extract(year from enddate)-extract(year from startdate))
  8  order by 1, 2
  9  /
MEMBER STARTDATE  ENDDATE    COUNTRY
------ ---------- ---------- ----------
123    01/01/2004 31/12/2004 nepal
123    01/01/2005 31/12/2005 nepal
123    01/01/2006 01/03/2006 nepal
245    01/01/2005 31/12/2005 india
245    01/01/2006 01/03/2006 india

5 rows selected.

Regards
Michel
Re: Replicate Rows [message #422935 is a reply to message #422540] Mon, 21 September 2009 05:17 Go to previous message
maheshmhs
Messages: 93
Registered: October 2008
Location: Nepal
Member
Thanks!
It's fast too.
Previous Topic: Explain Plan (merged 2)
Next Topic: NEED SQL HELP PLEASE
Goto Forum:
  


Current Time: Wed Dec 07 04:38:08 CST 2016

Total time taken to generate the page: 0.08046 seconds