Re: order a date dual select

From: Wanderley M Ceschim <wces123_at_yahoo.com>
Date: Sat, 21 Dec 2002 04:16:56 GMT
Message-ID: <YWRM9.29653$Nz5.747226_at_twister.austin.rr.com>

Don't use Union. Use "UNION ALL".

Anyway, there are other ways of achieving the same effect. Choose a table with enough rows in it and try something like this:

SELECT
   TO_DATE('30-MAR-2001') + (ROWNUM - 1) * 7 FROM
   SUCH_AND_SUCH
WHERE
   ROWNUM <= 100;

"SUCH_AND_SUCH" would be our pivot table. You can "borrow" a table that has nothing to do with your query (after all, you're not selecting anything from it anyway) and it would work just fine. ALL_OBJECTS (which isn't really a table), for instance, would work just fine:

SELECT
   TO_DATE('30-MAR-2001') + (ROWNUM - 1) * 7 Transaction_Date_LOV FROM
   ALL_OBJECTS
WHERE
   ROWNUM <= 100;

James Cummings wrote:

> Surely there's an easier way to get the date of
> every Friday between two dates.
> 
> 
>  -------------
> James Cummings
> "Chaos, panic, disorder.... my work is done."
> 
> "Alex Filonov" <afilonov_at_yahoo.com> wrote in message
> news:336da121.0212200818.62c3bd12_at_posting.google.com...
> 

>>ghoy_at_plusconsultancy.co.uk (gareth hoy) wrote in message

> 
> news:<82cb01aa.0212200337.6d636c43_at_posting.google.com>...
> 


>>>I have the following dual select statement:


>>>

>>>SELECT '30-MAR-2001' Transaction_Date_LOV FROM DUAL UNION

>>>SELECT '06-APR-2001' FROM DUAL UNION

>>>SELECT '12-APR-2001' FROM DUAL UNION

>>>SELECT '20-APR-2001' FROM DUAL UNION

>>>SELECT '27-APR-2001' FROM DUAL UNION

>>>SELECT '04-MAY-2001' FROM DUAL UNION

>>>SELECT '30-MAR-2001' FROM DUAL UNION

>>>SELECT '06-APR-2001' FROM DUAL UNION

>>>SELECT '12-APR-2001' FROM DUAL UNION

>>>SELECT '20-APR-2001' FROM DUAL UNION

>>>SELECT '27-APR-2001' FROM DUAL UNION

>>>SELECT '04-MAY-2001' FROM DUAL UNION

>>>SELECT '11-MAY-2001' FROM DUAL UNION

>>>SELECT '18-MAY-2001' FROM DUAL UNION

>>>SELECT '25-MAY-2001' FROM DUAL UNION

>>>SELECT '01-JUN-2001' FROM DUAL UNION

>>>SELECT '06-JUN-2001' FROM DUAL UNION

>>>SELECT '08-JUN-2001' FROM DUAL UNION

>>>SELECT '15-JUN-2001' FROM DUAL UNION

>>>SELECT '22-JUN-2001' FROM DUAL UNION

>>>SELECT '29-JUN-2001' FROM DUAL UNION

>>>SELECT '06-JUL-2001' FROM DUAL UNION

>>>SELECT '12-JUL-2001' FROM DUAL UNION

>>>SELECT '13-JUL-2001' FROM DUAL UNION

>>>SELECT '20-JUL-2001' FROM DUAL UNION

>>>SELECT '27-JUL-2001' FROM DUAL UNION

>>>SELECT '03-AUG-2001' FROM DUAL UNION

>>>SELECT '10-AUG-2001' FROM DUAL UNION

>>>SELECT '17-AUG-2001' FROM DUAL UNION

>>>SELECT '24-AUG-2001' FROM DUAL UNION

>>>SELECT '31-AUG-2001' FROM DUAL UNION

>>>SELECT '07-SEP-2001' FROM DUAL UNION

>>>SELECT '14-SEP-2001' FROM DUAL UNION

>>>SELECT '21-SEP-2001' FROM DUAL UNION

>>>SELECT '28-SEP-2001' FROM DUAL UNION

>>>SELECT '05-OCT-2001' FROM DUAL UNION

>>>SELECT '12-OCT-2001' FROM DUAL UNION

>>>SELECT '19-OCT-2001' FROM DUAL UNION

>>>SELECT '26-OCT-2001' FROM DUAL UNION

>>>SELECT '02-NOV-2001' FROM DUAL UNION

>>>SELECT '09-NOV-2001' FROM DUAL UNION

>>>SELECT '16-NOV-2001' FROM DUAL UNION

>>>SELECT '23-NOV-2001' FROM DUAL UNION

>>>SELECT '30-NOV-2001' FROM DUAL UNION

>>>SELECT '07-DEC-2001' FROM DUAL UNION

>>>SELECT '14-DEC-2001' FROM DUAL UNION

>>>SELECT '20-DEC-2001' FROM DUAL UNION

>>>SELECT '04-JAN-2002' FROM DUAL UNION

>>>SELECT '11-JAN-2002' FROM DUAL UNION

>>>SELECT '18-JAN-2002' FROM DUAL UNION

>>>SELECT '25-JAN-2002' FROM DUAL UNION

>>>SELECT '01-FEB-2002' FROM DUAL UNION

>>>SELECT '08-FEB-2002' FROM DUAL UNION

>>>SELECT '15-FEB-2002' FROM DUAL UNION

>>>SELECT '22-FEB-2002' FROM DUAL UNION

>>>SELECT '01-MAR-2002' FROM DUAL UNION

>>>SELECT '08-MAR-2002' FROM DUAL UNION

>>>SELECT '15-MAR-2002' FROM DUAL UNION

>>>SELECT '22-MAR-2002' FROM DUAL UNION

>>>SELECT '28-MAR-2002' FROM DUAL UNION

>>>SELECT '05-APR-2002' FROM DUAL UNION

>>>SELECT '12-APR-2002' FROM DUAL UNION

>>>SELECT '19-APR-2002' FROM DUAL UNION

>>>SELECT '26-APR-2002' FROM DUAL UNION

>>>SELECT '03-MAY-2002' FROM DUAL UNION

>>>SELECT '10-MAY-2002' FROM DUAL UNION

>>>SELECT '17-MAY-2002' FROM DUAL UNION

>>>SELECT '24-MAY-2002' FROM DUAL UNION

>>>SELECT '31-MAY-2002' FROM DUAL UNION

>>>SELECT '07-JUN-2002' FROM DUAL UNION

>>>SELECT '14-JUN-2002' FROM DUAL UNION

>>>SELECT '21-JUN-2002' FROM DUAL UNION

>>>SELECT '28-JUN-2002' FROM DUAL UNION

>>>SELECT '05-JUL-2002' FROM DUAL UNION

>>>SELECT '12-JUL-2002' FROM DUAL UNION

>>>SELECT '19-JUL-2002' FROM DUAL UNION

>>>SELECT '26-JUL-2002' FROM DUAL UNION

>>>SELECT '02-AUG-2002' FROM DUAL UNION

>>>SELECT '09-AUG-2002' FROM DUAL UNION

>>>SELECT '16-AUG-2002' FROM DUAL UNION

>>>SELECT '23-AUG-2002' FROM DUAL UNION

>>>SELECT '30-AUG-2002' FROM DUAL UNION

>>>SELECT '06-SEP-2002' FROM DUAL UNION

>>>SELECT '13-SEP-2002' FROM DUAL UNION

>>>SELECT '20-SEP-2002' FROM DUAL UNION

>>>SELECT '27-SEP-2002' FROM DUAL UNION

>>>SELECT '04-OCT-2002' FROM DUAL UNION

>>>SELECT '11-OCT-2002' FROM DUAL UNION

>>>SELECT '18-OCT-2002' FROM DUAL UNION

>>>SELECT '25-OCT-2002' FROM DUAL UNION

>>>SELECT '01-NOV-2002' FROM DUAL UNION

>>>SELECT '08-NOV-2002' FROM DUAL UNION

>>>SELECT '15-NOV-2002' FROM DUAL UNION

>>>SELECT '22-NOV-2002' FROM DUAL UNION

>>>SELECT '29-NOV-2002' FROM DUAL UNION

>>>SELECT '06-DEC-2002' FROM DUAL UNION

>>>SELECT '13-DEC-2002' FROM DUAL UNION

>>>SELECT '20-DEC-2002' FROM DUAL UNION

>>>SELECT '27-DEC-2002' FROM DUAL

>>>

>>>

>>>When the select statement is run ther order these values are displayed
>>> are not in the order in which they are selected. How do i get the
>>
>>And there is no guarantee they would. Read a little bit of relational
>>theory.
>>
>>
>>>values on the order they have been selected i.e. date order.
>>>
>>
>>Add
>>
>>ORDER BY 1
>>
>>to the end of your statement.
>>
>>
>>>Any help would be much appreciated.
>>>
>>>Gareth

> 
> 
> 

Received on Sat Dec 21 2002 - 05:16:56 CET