RE: how to generate the output w/o using temp table

Hi,

See if the following solution comes close:

Regards,
Charu.

create table tmp
(id number,  
 startdate date,  
 enddate date);

insert into tmp values (
1,   
to_date('10/01/2004', 'mm/dd/yyyy'),  
to_date('12/13/2004', 'mm/dd/yyyy'));

insert into tmp values (
2,   
to_date('12/01/2004', 'mm/dd/yyyy'),  
to_date('12/02/2005', 'mm/dd/yyyy'));

insert into tmp values (
3,   
to_date('12/03/2004', 'mm/dd/yyyy'),  
to_date('02/20/2005', 'mm/dd/yyyy')); 

Query:


select  id, 
        TO_CHAR(mnth, 'MM/YYYY')
from tmp a, 
          (select trunc(add_months(sysdate, - rownum + 1), 'MM') mnth 
           from all_objects
           union all
           select trunc(add_months(sysdate, rownum), 'MM') mnth 
           from all_objects) b
where b.mnth > a.startdate and a.mnth <= enddate



-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of mglim2@xxxxxxxxxxxx
Sent: Saturday, July 31, 2004 3:09 PM
To: oracle-l
Subject: how to generate the output w/o using temp table


Hi, 

I got a table w/ the ff: data 

id   startdate   enddate
1   10/01/2004  12/13/2004
2   12/01/2004  12/02/2005
3   12/03/2004  02/20/2005 


need to produce the ff:output 

mm/yyy            id
11/2004            1
12/2004            1
12/2004            2
12/2004            3
01/2005            3
02/2005            3 

how can i  produce this result w/o using a temporary table in my select 
stmt? 

 

Best Regards,
Grace Lim
Suy Sing Comm'l Corp 

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------


*********************************************************
Disclaimer:          

This message (including any attachments) contains 
confidential information intended for a specific 
individual and purpose, and is protected by law. 
If you are not the intended recipient, you should 
delete this message and are hereby notified that 
any disclosure, copying, or distribution of this
message, or the taking of any action based on it, 
is strictly prohibited.

*********************************************************
Visit us at http://www.mahindrabt.com

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: