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

From: Charudatta Joshi <joshic_at_mahindrabt.com>
Date: Sat, 31 Jul 2004 16:13:30 +0530
Message-ID: <MHEAIPLKCACENJKNJIALGENMCDAA.joshic@mahindrabt.com>

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of mglim2_at_softhome.net 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_at_freelists.org 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

Received on Sat Jul 31 2004 - 05:39:43 CDT