Bonjour,
j'ai crée une requête à partir de la fonction with et je voulais créer une vu utilisant cette requête mais apparemment il n'est pas possible de crée une vue avec la fonction with avez vous une solution ou une piste.

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
create view v_etat_parc as (
with nowm as(
select d.societe as soc_now,d.agence as ag_now,d.type as typ_now,d.affectation as aff_now,d.vehicule as vehic_now,
d.date_from as date_from_now,d.date_to as date_to_now,d.version as ver_now,d.date_sortie_parc as sortie_now
from d_vehicule d
where (d.date_sortie_parc is null or d.date_sortie_parc > sysdate)
and to_char(d.date_to)='31/12/99'
),
beforem1 as(
select d.societe as soc_m1,d.agence as ag_m1,d.type as typ_m1,d.affectation as aff_m1,d.vehicule as vehic_m1,
d.date_from as date_from_m1,d.date_to as date_to_m1,d.version as ver_m1,d.date_sortie_parc as sortie_m1
from d_vehicule d
where (d.date_sortie_parc is null or d.date_sortie_parc > last_day(add_months(trunc(sysdate,'mm'),-1)))
and ((to_char(d.date_to)='31/12/99' and d.date_from <last_day(add_months(trunc(sysdate,'mm'),-1))+1)
or  (d.date_to <= sysdate 
		and d.date_from = (select max(dd.date_from) 
							from d_vehicule dd
							where dd.vehicule=d.vehicule 
							and (dd.date_from <last_day(add_months(trunc(sysdate,'mm'),-1))+1)
 
                            							)))
and (d.date_from <last_day(add_months(trunc(sysdate,'mm'),-1)) and d.date_to>=last_day(add_months(trunc(sysdate,'mm'),-1)))
),
beforem2 as(							
select d.societe as soc_m2,d.agence as ag_m2,d.type as typ_m2,d.affectation as aff_m2,d.vehicule as vehic_m2,
d.date_from as date_from_m2,d.date_to as date_to_m2,d.version as ver_m2,d.date_sortie_parc as sortie_m2
from d_vehicule d
where (d.date_sortie_parc is null or d.date_sortie_parc > last_day(add_months(trunc(sysdate,'mm'),-2)))
and ((to_char(d.date_to)='31/12/99' and d.date_from <last_day(add_months(trunc(sysdate,'mm'),-2))+1)
or  (d.date_to <= sysdate 
		and d.date_from = (select max(dd.date_from) 
							from d_vehicule dd
							where dd.vehicule=d.vehicule 
							and (dd.date_from <last_day(add_months(trunc(sysdate,'mm'),-2))+1)
 
                            							)))
and (d.date_from <last_day(add_months(trunc(sysdate,'mm'),-2)) and d.date_to>=last_day(add_months(trunc(sysdate,'mm'),-2)))
)
select unique n.soc_now as societe_m, n.ag_now as agence_m,
d.vehicule, d.type, n.aff_now as affectation_m, n.date_from_now as date_from_m,
n.date_to_now as date_to_m,n.ver_now as version_m, n.sortie_now,
b.soc_m1 as societe_m1, b.ag_m1 as agence_m1,
b.aff_m1 as affectation_m1, b.date_from_m1 as date_from_m1,
b.date_to_m1 as date_to_m1, b.ver_m1 as version_m1, b.sortie_m1,
bb.soc_m2 as societe_m2, bb.ag_m2 as agence_m2,
bb.aff_m2 as affectation_m2, bb.date_from_m2 as date_from_m2,
bb.date_to_m2 as date_to_m2, bb.ver_m2 as version_m2, bb.sortie_m2
from d_vehicule d, nowm n, beforem1 b, beforem2 bb
where d.vehicule=n.vehic_now(+)
and d.vehicule=b.vehic_m1(+)
and d.vehicule=bb.vehic_m2(+)
)

Merci d'avance pour votre aide.