Discussion :

[ilalaina]

Bonjour.

J'aimerais sortir la liste de tous les jours inclus entre deux dates.
J'ai trouvé le forum suivant qui est un peu similaire : obtenir les 28 derniers jours du mois à partir de la date actuelle mais je n'arrive pas à adapter la requête à ma situation.

J'ai une table contenant des colonnes dates dont le contenu est par exemple :
01/01/2009
02/01/2009
05/01/2009
et j'aimerais donc afficher comme résultat :
01/01/2009
02/01/2009
03/01/2009
04/01/2009
05/01/2009

Est-ce que quelqu'un peut m'aider?

Merci.

[nuke_y]

Problème classique (axe de dimension Temps dans un cube) très compliqué à résoudre car tu veux créer des lignes supplémentaires.

Il y a plusieurs solutions qui dépendent du contexte d'utilisation : tu fais une requête en script ? Avec un requêteur (BO, Cognos, Qlikview), un Cube (Essbase, Cognos) ? Avec un ETL (ODI/sunopsis, Genio, SSIS, Talend) ?

Parmi les solutions:
- utiliser une table très peuplée (comme ALL_OBJECTS) pour générer une liste de lignes
- utiliser une requête récursive sur DUAL
- PL/SQL
- Créer une table CALENDRIER avec 1 ligne par jour (attention il faudra la maintenir).

[Waldar]

- Créer une table CALENDRIER avec 1 ligne par jour (attention il faudra la maintenir).

Qu'entendez-vous par la maintenir ?

[GoLDoZ]

Une requête SQL ne suffit-elle pas à résoudre son problème?

[fatsora]

Pour ajouter a tout ce qui a été ecris, quelle version Oracle tu as ???

La reponse a ta question est complètement dépendante de la version Oracle ....

[ilalaina]

La reponse a ta question est complètement dépendante de la version Oracle ....

J'utilise Oracle 9i.

J'arrive à le faire simple avec pl/sql :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- Created on 09/10/2009 by ILALAINA 
DECLARE
  TYPE t_dates IS TABLE OF DATE;
  v_dates     t_dates;
  v_date_min  DATE;
  v_date_max  DATE;
  v_date_temp DATE;
  i           PLS_INTEGER := 1;
BEGIN
  v_dates := t_dates();
  SELECT MIN(oper_date), MAX(oper_date)
    INTO v_date_min, v_date_max
    FROM operations;
  v_date_temp := v_date_min;
  WHILE (v_date_temp <= v_date_max) LOOP
    v_dates.EXTEND;
    v_dates(i) := v_date_temp;
    v_date_temp := v_date_temp + 1;
    i := i + 1;
  END LOOP;
  -- Et la collection v_dates sera la valeur de retour de la procédure.
  /*  for j in 1 .. v_dates.count loop
  dbms_output.put_line(v_dates(j));
  end loop;*/
END;

Mais je voudrais le faire avec une requête SQL, peut-être avec des requêtes hiérarchiques ...

[nuke_y]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT
TO_DATE('01/01/2009', 'DD/MM/YYYY') + level - 1
FROM dual
connect BY level <= 1 + (TO_DATE('08/01/2009', 'DD/MM/YYYY') - TO_DATE('01/01/2009', 'DD/MM/YYYY'))

[Waldar]

Connect by level ne fonctionne qu'à partir de la 10g il me semble.

[ilalaina]

Si si.

Connect by level marche sur 9i. Mais je ne peux pas figer la requête. Il faut que les bornes sur l'intervalle de date soit le max et le min dans la table. Mais j'obtiens cette erreur :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0 
Connected as ilalaina
 
SQL> 
SQL> SELECT TO_DATE(oper_date) + LEVEL - 1
  2    FROM operations
  3  CONNECT BY LEVEL <= 1 + (select max(oper_date) - min(oper_date) from operations)
  4  ;
 
SELECT TO_DATE(oper_date) + LEVEL - 1
  FROM operations
CONNECT BY LEVEL <= 1 + (select max(oper_date) - min(oper_date) from operations)
 
ORA-01473: les sous-interrogations ne sont pas autorisées dans une clause CONNECT BY
 
SQL>

[ilalaina]

En fait voilà où je veux en venir. Je l'illustre avec un petit exemple simple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0 
Connected as ilalaina
 
SQL> 
SQL> drop table operations;
 
Table dropped
SQL> drop table utilisateurs;
 
Table dropped
SQL> create table UTILISATEURS(UTIL_LOGIN VARCHAR2(64), UTIL_NOM VARCHAR2(128));
 
Table created
SQL> create table OPERATIONS(OPER_ID NUMBER, OPER_MONTANT NUMBER, UTIL_LOGIN VARCHAR2(64), OPER_DATE DATE);
 
Table created
SQL> insert into UTILISATEURS values ('toto', 'toto toto');
 
1 row inserted
SQL> insert into UTILISATEURS values ('tata', 'tata tata');
 
1 row inserted
SQL> insert into UTILISATEURS values ('titi', 'titi titi');
 
1 row inserted
SQL> commit;
 
Commit complete
SQL> insert into OPERATIONS values (1, 10, 'toto', to_date('09-10-2009', 'dd-mm-yyyy'));
 
1 row inserted
SQL> insert into OPERATIONS values (2, 20, 'toto', to_date('09-10-2009', 'dd-mm-yyyy'));
 
1 row inserted
SQL> insert into OPERATIONS values (3, 30, 'tata', to_date('09-10-2009', 'dd-mm-yyyy'));
 
1 row inserted
SQL> insert into OPERATIONS values (4, 40, 'toto', to_date('08-10-2009', 'dd-mm-yyyy'));
 
1 row inserted
SQL> insert into OPERATIONS values (5, 50, 'toto', to_date('01-10-2009', 'dd-mm-yyyy'));
 
1 row inserted
SQL> commit;
 
Commit complete
SQL> select * from utilisateurs;
 
UTIL_LOGIN                                                       UTIL_NOM
---------------------------------------------------------------- --------------------------------------------------------------------------------
toto                                                             toto toto
tata                                                             tata tata
titi                                                             titi titi
SQL> select * from operations;
 
   OPER_ID OPER_MONTANT UTIL_LOGIN                                                       OPER_DATE
---------- ------------ ---------------------------------------------------------------- -----------
         1           10 toto                                                             09/10/2009
         2           20 toto                                                             09/10/2009
         3           30 tata                                                             09/10/2009
         4           40 toto                                                             08/10/2009
         5           50 toto                                                             01/10/2009
SQL> SELECT utilisateurs.util_login, operations.oper_date, SUM(operations.oper_montant)
  2    FROM utilisateurs, operations
  3   WHERE utilisateurs.util_login = operations.util_login(+)
  4   GROUP BY utilisateurs.util_login, operations.oper_date;
 
UTIL_LOGIN                                                       OPER_DATE   SUM(OPERATIONS.OPER_MONTANT)
---------------------------------------------------------------- ----------- ----------------------------
tata                                                             09/10/2009                            30
titi                                                                         
toto                                                             01/10/2009                            50
toto                                                             08/10/2009                            40
toto                                                             09/10/2009                            30
 
SQL>

Au lieu d'écrire un long bloc pl/sql comme ci-dessous :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
 
DECLARE
 
  TYPE t_dates IS TABLE OF DATE;
  v_dates     t_dates;
  v_date_min  DATE;
  v_date_max  DATE;
  v_date_temp DATE;
 
  TYPE t_login IS TABLE OF VARCHAR2(64);
  v_login t_login;
  v_req   VARCHAR2(1024);
  vr_sum  PLS_INTEGER;
 
BEGIN
 
  SELECT DISTINCT util_login BULK COLLECT INTO v_login FROM utilisateurs;
  v_req := 'select nvl(sum(oper_montant), 0) from operations where util_login = :1 and oper_date = :2';
 
  SELECT MIN(oper_date), MAX(oper_date)
    INTO v_date_min, v_date_max
    FROM operations;
  v_date_temp := v_date_min;
  WHILE (v_date_temp <= v_date_max) LOOP
    FOR i IN v_login.FIRST .. v_login.LAST LOOP
      EXECUTE IMMEDIATE v_req
        INTO vr_sum
        USING v_login(i), v_date_temp;
      dbms_output.put_line(v_date_temp || ' || ' || v_login(i) || ' || ' ||
                           vr_sum);
    END LOOP;
    v_date_temp := v_date_temp + 1;
  END LOOP;
 
END;

01/10/09 || tata || 0
01/10/09 || titi || 0
01/10/09 || toto || 50
02/10/09 || tata || 0
02/10/09 || titi || 0
02/10/09 || toto || 0
03/10/09 || tata || 0
03/10/09 || titi || 0
03/10/09 || toto || 0
04/10/09 || tata || 0
04/10/09 || titi || 0
04/10/09 || toto || 0
05/10/09 || tata || 0
05/10/09 || titi || 0
05/10/09 || toto || 0
06/10/09 || tata || 0
06/10/09 || titi || 0
06/10/09 || toto || 0
07/10/09 || tata || 0
07/10/09 || titi || 0
07/10/09 || toto || 0
08/10/09 || tata || 0
08/10/09 || titi || 0
08/10/09 || toto || 40
09/10/09 || tata || 30
09/10/09 || titi || 0
09/10/09 || toto || 30

n'y a t-il pas moyen de faire cette requête avec du simple sql?

Merci.

[boutade80]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
select * from
(
     select to_char(sysdate-level,'YYYY-MM-DD') as dt from dual
     connect by level <300000
     order by dt
) rq
where rq.dt >=
(
     select to_char(min(oper_date),'YYYY-MM-DD') from operations
     group by 1
)
and rownum <=
(
     select trunc((max(oper_date) -min(oper_date)))
     from operations
     group by 1
)

La chose affreuse reside ds le connect by 300000. Si ta date min est inferieur a 300 000 jours par rapport a today ca marche pas.

[nuke_y]

Tu peux mais pas sans bricoler un peu avec la table dual en connect by ou en utilisant une table avec beaucoup de lignes comme all_objects ou une table CALENDRIER que tu alimentes toi même.

[Waldar]

Tient bizarre chez moi ça ne fonctionne pas.
Là où je travaille j'ai une 9i et une 11g.

Sur 9i :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select banner from sys.v_$version;
 
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE	9.2.0.8.0	Production
TNS for 32-bit Windows: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production
 
 
select level from dual
connect by level <= 10;
 
LEVEL
1

Sur 11g :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
select banner from sys.v_$version;
 
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE	11.1.0.7.0	Production
TNS for 64-bit Windows: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production
 
LEVEL
1
2
3
4
5
6
7
8
9
10

Peut-être à cause des sous-versions ?


boutade80 > toute la requête est affreuse !


ilalaina > ça ne vous aidera pas beaucoup sauf si vous me dites que le connect by level fonctionne chez vous, mais en 11g la requête suivante fonctionne (edit : ça fonctionne en 9i aussi, je vais investiguer) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
With UTILISATEUR AS
(
select 'toto' as UTIL_LOGIN, 'toto toto' as UTIL_NOM from dual union all
select 'tata'              , 'tata tata'             from dual union all
select 'titi'              , 'titi titi'             from dual
)
  ,  OPERATION AS
(
select 1 as OPER_ID, 10 as OPER_MONTANT, 'toto' as UTIL_LOGIN, date '2009-10-09' as oper_date from dual union all
select 2           , 20                , 'toto'              , date '2009-10-09'              from dual union all
select 3           , 30                , 'tata'              , date '2009-10-09'              from dual union all
select 4           , 40                , 'toto'              , date '2009-10-08'              from dual union all
select 5           , 50                , 'toto'              , date '2009-10-01'              from dual
)
  ,  BORNE AS
(
select min(oper_date) as oper_date_min, max(oper_date) as oper_date_max
from operation
)
  , PERIODE AS
(
select oper_date_min + level - 1 as dt
from BORNE 
connect by level <= oper_date_max - oper_date_min + 1
)
select
    pe.dt as oper_date,
    ut.util_login,
    coalesce(sum(op.oper_montant), 0) as oper_montant
from
    periode pe
    cross join UTILISATEUR ut
    left outer join OPERATION op
      on op.oper_date = pe.dt
     and op.util_login = ut.util_login
group by
    pe.dt,
    ut.util_login
order by
    pe.dt asc,
    ut.util_login asc;

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
OPER_DATE	UTIL_LOGIN	OPER_MONTANT
01/10/2009	tata	0
01/10/2009	titi	0
01/10/2009	toto	50
02/10/2009	tata	0
02/10/2009	titi	0
02/10/2009	toto	0
03/10/2009	tata	0
03/10/2009	titi	0
03/10/2009	toto	0
04/10/2009	tata	0
04/10/2009	titi	0
04/10/2009	toto	0
05/10/2009	tata	0
05/10/2009	titi	0
05/10/2009	toto	0
06/10/2009	tata	0
06/10/2009	titi	0
06/10/2009	toto	0
07/10/2009	tata	0
07/10/2009	titi	0
07/10/2009	toto	0
08/10/2009	tata	0
08/10/2009	titi	0
08/10/2009	toto	40
09/10/2009	tata	30
09/10/2009	titi	0
09/10/2009	toto	30

[Waldar]

C'est marrant ça, en 9i :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
select level
from (select 10 nm from dual)
connect by level <= nm;
 
LEVEL
1

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select level
from (select min(10) nm from dual)
connect by level <= nm;
 
LEVEL
1
2
3
4
5
6
7
8
9
10

Ca donne quoi chez vous ?

[orafrance]

normal... tu voulais surement tester :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT  min(level)
FROM (SELECT 10 nm FROM dual)
connect BY level <= nm

min(10) from dual = 10... ça empêche pas le CONNECT BY

[ilalaina]

Waldar < Ca marche très bien chez-moi :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
 
Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0 
Connected as ilalaina
 
SQL> SELECT banner FROM sys.v_$version;
 
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE	9.2.0.1.0	Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production
 
SQL> 
SQL> SELECT level
  2  FROM (SELECT 10 nm FROM dual)
  3  connect BY level <= nm;
 
     LEVEL
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
 
10 rows selected
 
SQL>

Et votre réponse aussi résout éxactement mon problème.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
 
WITH UTILISATEUR AS
(
SELECT distinct(util_login) from utilisateurs
)
  ,  OPERATION AS
(
select * from operations
)
  ,  BORNE AS
(
SELECT min(oper_date) AS oper_date_min, max(oper_date) AS oper_date_max
FROM operation
)
  , PERIODE AS
(
SELECT oper_date_min + level - 1 AS dt
FROM BORNE 
connect BY level <= oper_date_max - oper_date_min + 1
)
SELECT
    pe.dt AS oper_date,
    ut.util_login,
    coalesce(sum(op.oper_montant), 0) AS oper_montant
FROM
    periode pe
    CROSS JOIN UTILISATEUR ut
    LEFT OUTER JOIN OPERATION op
      ON op.oper_date = pe.dt
     AND op.util_login = ut.util_login
GROUP BY
    pe.dt,
    ut.util_login
ORDER BY
    pe.dt ASC,
    ut.util_login ASC;

Merci beaucoup.

[Waldar]

Tant mieux alors !
Pas besoin de répéter les deux premiers with, c'était pour créer le jeu d'essai.

orafrance > non non j'ai bien réussi mes copier / coller, je trouve celà étrange aussi.

[orafrance]

J'vois pas en quoi c'est étrange

[Waldar]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
select 10 nm from dual;
 
NM
10
 
 
select min(10) nm from dual;
 
NM
10
 
select level
from (select 10 nm from dual)
connect by level <= nm;
 
LEVEL
1
 
select level
from (select min(10) nm from dual)
connect by level <= nm;
 
LEVEL
1
2
3
4
5
6
7
8
9
10

Moi je trouve celà étrange

[GoLDoZ]

Effectivement, le 3ème résultat est étrange.