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

From: <mglim2_at_softhome.net>
Date: Sat, 31 Jul 2004 20:08:02 -0600
Message-ID: <courier.410C5082.00003610@softhome.net>

will all users be able to access the all objects. my user wants to query for certain range of month who are on-vacation? example: user enter starting date 10/2004 ending date: 02/2005 result:

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

Charudatta Joshi writes:

>
> 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
>

---

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
-----------------------------------------------------------------

Received on Sat Jul 31 2004 - 21:03:55 CDT