April 04, 2007

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:

Rosario Vigilante said...

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

Falko ten Bosch said...

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;