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

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