[Oracle/SQL] Lijst van de afgelopen 30 dagen mbv sysdate? *

[Toink]

In oracle kun je de datum weergeven met:

code:

select 
    to_char(sysdate,'DY DD/MM/YYYY'),             
from 
    dual;

Dit geeft:

code:

TUE 27/01/2009

Hoe kan ik een query schrijven die een lijst met de afgelopen 30 dagen weergeeft? Dus:

code:

TUE 27/01/2009
MON 26/01/2009
SUN 25/01/2009
SAT 24/01/2009
FR 23/01/2009
(....enz...)

Dus niet als rij maar als kolom, iemand een idee? Ik kan het niet uit een tabel halen en ik kan ook geen (tijdelijke) tabel maken. Het moet dus in SQL...

[ Voor 11% gewijzigd door Toink op 27-01-2009 16:17 ]

[Niemand_Anders]

Kijk eens naar top / limit (afhankelijk van welke database je gebruikt) en 'order by ... desc'.
Daarmee moet je er wel uit kunnen komen.

[RobIII]

Ik ben niet echt bekend met Oracle, maar in T-SQL kun je DateAdd gebruiken (met een negatieve 'number' parameter) zoals bijv:

SQL:

Select dateadd(d, -30, GetDate()) as date

Dan kun je in een where-clause iets gebruiken als:

SQL:

Select * from orders where order_date > dateadd(d, -30, GetDate())

Wat jij echter wil zou ik niet in een query willen (of gaan) oplossen, dan krijg je (afaik) 30 unions Iets als:

SQL:

select dateadd(d, -1, GetDate()) as date
union
select dateadd(d, -2, GetDate()) as date
union
...
union
select dateadd(d, -30, GetDate()) as date
order by date desc

Waar heb je het precies voor nodig?

Ik heb by the way even je topictitel aangevuld [SQL] -> [Oracle/SQL]

[ Voor 7% gewijzigd door RobIII op 27-01-2009 16:31 ]

[justmental]

Je kunt een tabel gebruiken waar je zeker weet dat er 30 rijen in zitten.
Of als je het netjes wilt doen kun je een array als table casten in je query of de model clausule gebruiken:
http://www.psoug.org/reference/model_clause.html

[DamadmOO]

Een andere oplossing in plaats van de union is door gebruik te maken van een table function, maar dat is ook niet de mooiste oplossing.

Waarom kan je geen eigen tabel aanmaken met daarin een lijst met alle datums van een aantal jaar, waar je dan gewoon een simpele select op kan toepassen? Ik heb laatst namelijk toevallig dezelfde eis gehad, maar heb toen een datum tabel er gelukkig wel door kunnen drukken

[Toink]

Probleem is als volgt:

Ik heb twee tabellen van (trage) machines die op datum gegroepeerd worden. Die moeten naast elkaar staan op rijniveau per datum.

Omdat soms de ene tabel data mist en soms de andere, kan ik er niet één als "leading" nemen en de ander erop joinen. Gezien de grootte van de database is het ook geen optie om maar gewoon even een select distinct te doen beide tabellen en dat bij elkaar te stoppen.

Om die reden heb ik dus een simpele systeemquery nodig waarmee ik dit probleem omzeil en een lijst van 30 dagen kan genereren waarop ik de resultaten van beide andere tabellen join.

@ROBIII: Precies wat je onderaan zegt. Een union kan wel en werkt, maar dat is echt heel ranzig en het moet beter kunnen.

[ Voor 9% gewijzigd door Toink op 27-01-2009 16:36 ]

[justmental]

Een full outer join werkt tegenwoordig ook op Oracle hoor, gewoon de ansi notatie gebruiken:
select t1.*, t2.* from t1 full outer join t2 on (key1=key2)

[Toink]

justmental schreef op dinsdag 27 januari 2009 @ 16:40:
Een full outer join werkt tegenwoordig ook op Oracle hoor, gewoon de ansi notatie gebruiken:
select t1.*, t2.* from t1 full outer join t2 on (key1=key2)

Helaas niet bij Oracle 8.1.7.4....

/edit: idd, 8.2 bestaat niet.

[ Voor 6% gewijzigd door Toink op 27-01-2009 16:48 ]

[justmental]

Toink schreef op dinsdag 27 januari 2009 @ 16:41:
Helaas niet bij Oracle 8.2....

8.2 bestaat niet eens

als je een 8i versie bedoelt (zoals 8.1.7) dan blijven de tabel of de table cast oplossing over.

[ Voor 23% gewijzigd door justmental op 27-01-2009 16:46 ]

[Acid__Burn]

Al gekeken naar een WHILE LOOP? Als je een counter maakt:

WHILE @COUNTER < 30
DAY - 1
SET @COUNTER = @COUNTER - 1

[RobIII]

Acid__Burn schreef op dinsdag 27 januari 2009 @ 16:48:
Al gekeken naar een WHILE LOOP? Als je een counter maakt:

WHILE @COUNTER < 30
DAY - 1
SET @COUNTER = @COUNTER - 1

En hoe wou je daar op joinen?
En @Counter < 30 en dan @COUNTER = @COUNTER - 1 doen gaat denk ik ook niet werken Ik denk dat je + bedoelt of Counter > 0 waarbij counter een initiele value van 30 heeft ofzo

[ Voor 12% gewijzigd door RobIII op 27-01-2009 16:57 ]

[justmental]

code:

in db:
create type number_type as table of number

pl/sql:
declare
  t_num number_t;
begin
  t_num := number_t();
  for i in 1..30
  loop
    t_num.extend;
    t_num(i) := i;
  end loop;

  for rec in (select trunc(sysdate-column_value) datum
              from table (cast (t_num as number_t))
              order by 1
             )
  loop
    dbms_output.put_line (to_char(rec.datum,'dd-mm-yyyy'));
  end loop;
end;

[Toink]

@Justmental: ik heb helaas geen rechten.... ik ben nu aan het kijken naar deze optie maar krijg nog een fout:

http://www.orafaq.com/wiki/Oracle_Row_Generator_Techniques

"Connect by loop" in user data

[Toink]

Fijn, een oplossing:

code:

select 
    dt
from 
    (select 
        to_date(sysdate,'dd-mon-yyyy')+level-1 dt 
    from 
        dual 
    connect by level <= 30 and 
    prior dbms_random.random is not null)

[ Voor 4% gewijzigd door Toink op 27-01-2009 17:25 ]

[Acid__Burn]

RobIII schreef op dinsdag 27 januari 2009 @ 16:53:
[...]

En hoe wou je daar op joinen?
En @Counter < 30 en dan @COUNTER = @COUNTER - 1 doen gaat denk ik ook niet werken Ik denk dat je + bedoelt of Counter > 0 waarbij counter een initiele value van 30 heeft ofzo

Is inderdaad mijn fout. Moet zijn:

Maak counter met een heel getal (in dit geval 30 dagen terug, dus counter = 30)

while @counter > 0
set @day = @day - 1
select @day
set @counter = @counter - 1

Op die manier kun je dus (eventueel met een var bij het aanroepen van de sp) een X aantal dagen terug rekenen.


_{Sorry RobIII voor de foute post. Wilde nog even snel een hint geven aan de TS voordat ik geen tijd meer zou hebben (kwam een meeting aan )}

[winkbrace]

Toink schreef op dinsdag 27 januari 2009 @ 17:20:
Fijn, een oplossing:

code:

select 
    dt
from 
    (select 
        to_date(sysdate,'dd-mon-yyyy')+level-1 dt 
    from 
        dual 
    connect by level <= 30 and 
    prior dbms_random.random is not null)

oh, dat is ZO dirty! Heerlijk!

edit: Nouja.. dirty... In ieder geval zeer slim gebruik van de mogelijkheden van Oracle
edit2: Omdat ik wel onder de indruk van deze query was heb ik em proberen te begrijpen. Hij is wat nodeloos ingewikkeld gemaakt. Eigenlijk staat er dit:

SQL:

select trunc(sysdate + level - 1)  dt 
from   dual 
connect by prior dbms_random.random is not null -- prior (= parent node) is altijd aanwezig
and        level <= 30                          -- stop bij level 30

[ Voor 43% gewijzigd door winkbrace op 28-01-2009 16:57 ]