| Query to generate Weekends [message #31247] |
Fri, 18 June 2004 02:13  |
Vinodha Kaleeswaran
Messages: 21
Registered: June 2004
|
Junior Member |
|
|
Hi,
I want a query which would generate all the saturdays of the year.
I have comeup with a query , which seems to be very slow while retrieving only the last record. Can anyone explain what could be the reason and suggest me any other query which would serve the same purpose
SELECT *
FROM (select TRUNC(next_day( to_date('01-jan-2004') + 7*(rownum-1) ,'SATURDAY')) WEEKEND_dATE
from all_objects )
where WEEKEND_dATE <= to_date('31-dec-2004')
|
|
|
|
| Re: Query to generate Weekends [message #31248 is a reply to message #31247] |
Fri, 18 June 2004 03:08  |
Thomas G
Messages: 58
Registered: April 2003
|
Member |
|
|
It could be that first the inner select is executed, which returns an endless number of rows, and then the where.
This :
SELECT TRUNC(next_day( to_date('01-jan-2004') + 7*(rownum-1) ,'SATURDAY'))
FROM all_objects
WHERE TRUNC(next_day( to_date('01-jan-2004') + 7*(rownum-1) ,'SATURDAY')) <= To_Date('01-dec-2005');
seems to be pretty quick in comparison.
|
|
|
|
|
|
| Re: Query to generate Weekends [message #31258 is a reply to message #31249] |
Sat, 19 June 2004 04:59 |
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
|
Senior Member |
|
|
Just for fun, you could also try a pipelined function. It's quite a common requirement to be able to generate a number of rows, so I have a ROW_GENERATOR() function as:
CREATE OR REPLACE TYPE INTEGER_TT AS TABLE OF INTEGER
/
CREATE OR REPLACE FUNCTION row_generator
( p_rows INTEGER )
RETURN INTEGER_TT
DETERMINISTIC
PIPELINED
AS
BEGIN
FOR i IN 1..ABS(p_rows) LOOP
PIPE ROW(i);
END LOOP;
RETURN;
END row_generator;
Then you just need
SELECT NEXT_DAY(TRUNC(SYSDATE,'Year') -7, 'Saturday') + column_value * 7 AS day
FROM TABLE(ROW_GENERATOR(52)); |
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
