While developing applications I need often list-of-values, which have a result-set of years, months or days.
You can hard-code those selects each time in each form, but much more elegant is to create views, which do the whole work.
Here are 3 views for years, months and days:
CREATE OR REPLACE FORCE VIEW YEARS_V
(YDATE) AS
SELECT add_months (trunc (sysdate, 'YYYY'), 12 * (50 - Level))
FROM Dual
CONNECT BY Level <= 100;
CREATE OR REPLACE FORCE VIEW MONTHS_V
(MDATE) AS
SELECT add_months (trunc (sysdate, 'MM'), 500 - Level)
FROM Dual
CONNECT BY Level <= 1000;
CREATE OR REPLACE FORCE VIEW DAYS_V
(DDATE) AS
SELECT trunc (sysdate) + 15000 - Level
FROM Dual
CONNECT BY Level <= 30000;
Those Views helps us to Select Data for the
- actual year +/- 50 years
- actual month +/- 500 months
- actual day +/- 15000 days
In a Record-Group you can use those views:
LOV of the next 10 years
SELECT YDATE
FROM Years_V
WHERE YDATE BETWEEN trunc (sysdate, 'YYYY')
AND add_months (trunc (sysdate, 'YYYY'), 10*12);
LOV of the last 30 and the next 10 days
SELECT DDATE
FROM Days_V
WHERE DDATE BETWEEN trunc (sysdate-30) AND trunc (sysdate+10);
Try it
Gerd
