Multi-Select from DUAL
An easy way to generate records from scratch is using an easy CONNECT BY against DUAL.
e.g. you need a Forms-LOV which shows the last 12 months.
So you have to create a record-group-select which gives you exactly 12 records. After that you combine it with sysdate. Let's see:
SELECT Level LVL
FROM Dual
CONNECT BY Level <= 12;
then you integrate the sysdate into the statement:
SELECT add_months (trunc (sysdate, 'MM'), -1*Level) Month
FROM Dual
CONNECT BY Level <= 12;
MONTH
--------
01.03.07
01.02.07
01.01.07
01.12.06
01.11.06
01.10.06
01.09.06
01.08.06
01.07.06
01.06.06
01.05.06
isn't that a pretty easy solution for getting generically the last 12 months?
2 comments:
Also, for one month
select days.dt
from
(select to_date('01/02/2006','dd/mm/yyyy') + ones.x + tens.x dt
from
(
select 0 x from dual union all
select 1 x from dual union all
select 2 x from dual union all
select 3 x from dual union all
select 4 x from dual union all
select 5 x from dual union all
select 6 x from dual union all
select 7 x from dual union all
select 8 x from dual union all
select 9 x from dual) ones,
(select 0 x from dual union all
select 10 x from dual union all
select 20 x from dual union all
select 30 x from dual) tens ) days
WHERE days.dt between '01/02/2006' and '28/02/2006'
group by days.dt
ORDER BY 1
Rosario Vigilante
rosario.vigilante@campolongohospital.it
I tried this to create a "date table" without using PL/SQL...
Set your own limit as preferred:
SELECT limits.start_day + counter.x Dates
FROM (SELECT TO_DATE('01.01.1990') -1 start_day,
TO_DATE('31.12.2015') end_day FROM DUAL) Limits
CROSS JOIN (SELECT Level x FROM Dual CONNECT BY Level <= 10000000) Counter
WHERE Counter.x <= Limits.end_day - Limits.start_day;
Post a Comment