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:

  1. AnonymousApril 04, 2007

    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

    ReplyDelete
  2. Falko ten BoschJune 15, 2009

    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;

    ReplyDelete