RE: how to generate the output w/o using temp table
- From: "Charudatta Joshi" <joshic@xxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 31 Jul 2004 16:13:30 +0530
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 //www.freelists.org/archives/oracle-l/
FAQ is at //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 //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
