Discussion :

[StringBuilder]

Bonjour à tous,

J'ai une fonction qui retourne un type "table".
Elle prend en paramètre des informations qui sont contenues dans une autre table.

Seulement, lorsque je tente de faire une jointure en passant en paramètres des champs plutôt que des constantes à ma fonction, j'obtiens l'erreur suivante :

Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "devis.date_debut" could not be bound.
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "devis.date_fin" could not be bound.

Si je ne change rien, mise à par mettre des constantes à la place, ça marche sans problème.

Comment ça se fait ?

Voici ma base de données complète + jeu de test :

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
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
drop function ListeJours;
go
drop table devis_ligne;
go
drop table devis;
go
drop table Prix;
go
drop table Saison_Date;
go
drop table Saison;
go
drop table Produit;
go
 
create table Produit
(
  id int primary key clustered not null,
  nom varchar(40) not null
);
 
create table Saison
(
  id int primary key clustered not null,
  nom varchar(20) not null
);
 
create table Saison_Date
(
  date_debut datetime not null,
  date_fin datetime not null,
  saison_id int references Saison(id) not null
);
 
alter table Saison_Date add constraint pk_Saison_Date primary key clustered (date_debut, date_fin);
 
create table Prix
(
  produit_id int references Produit(id) not null,
  saison_id int references Saison(id) not null,
  montant money null
);
 
alter table Prix add constraint pk_Prix primary key clustered (produit_id, saison_id);
 
create table devis
(
  id int primary key clustered not null,
  date_debut datetime not null,
  date_fin datetime not null,
  nom varchar(40) not null
);
 
create table devis_ligne
(
  devis_id int references devis(id) not null,
  produit_id int references produit(id) not null,
  quantite int not null
);
 
alter table devis_ligne add constraint pk_devis_ligne primary key clustered (devis_id, produit_id);
go
 
create function ListeJours(
		@_dateDeb datetime
		, @_dateFin datetime
	)
	RETURNS @tb_dates TABLE
	(
		dates datetime NOT NULL
	)
	WITH SCHEMABINDING
AS
BEGIN
	WHILE @_dateDeb <= @_dateFin
	BEGIN
		INSERT INTO @tb_dates (dates) VALUES (@_dateDeb)
		SELECT @_dateDeb = DATEADD(day, 1, @_dateDeb)
	END
	RETURN
END;
go
 
insert into Produit (id, nom) values (1, 'Emplacement');
insert into Produit (id, nom) values (2, 'Véhicule moteur');
insert into Produit (id, nom) values (3, 'Campeur');
insert into Produit (id, nom) values (4, 'Enfant (2 à 6 ans)');
insert into Produit (id, nom) values (5, 'Enfant de moins de 2 ans');
insert into Produit (id, nom) values (6, 'Branchement électrique 10 Ampères');
insert into Produit (id, nom) values (7, 'Animal domestique');
 
insert into Saison (id, nom) values (1, 'Haute saison');
insert into Saison (id, nom) values (2, 'Basse saison');
 
insert into Saison_Date (date_debut, date_fin, saison_id) values ('1900-07-01', '1900-08-31', 1);
insert into Saison_Date (date_debut, date_fin, saison_id) values ('1900-05-08', '1900-06-30', 2);
insert into Saison_Date (date_debut, date_fin, saison_id) values ('1900-09-01', '1900-09-30', 2);
 
insert into Prix (produit_id, saison_id, montant) values (1,1,3);
insert into Prix (produit_id, saison_id, montant) values (1,2,2.5);
insert into Prix (produit_id, saison_id, montant) values (2,1,2);
insert into Prix (produit_id, saison_id, montant) values (2,2,2);
insert into Prix (produit_id, saison_id, montant) values (3,1,3);
insert into Prix (produit_id, saison_id, montant) values (3,2,2.5);
insert into Prix (produit_id, saison_id, montant) values (4,1,1.5);
insert into Prix (produit_id, saison_id, montant) values (4,2,1.3);
insert into Prix (produit_id, saison_id, montant) values (5,1,null);
insert into Prix (produit_id, saison_id, montant) values (5,2,null);
insert into Prix (produit_id, saison_id, montant) values (6,1,3);
insert into Prix (produit_id, saison_id, montant) values (6,2,3);
insert into Prix (produit_id, saison_id, montant) values (7,1,.5);
insert into Prix (produit_id, saison_id, montant) values (7,2,.5);
 
insert into devis (id, date_debut, date_fin, nom) values (1, '1900-06-15', '1900-07-03', 'StringBuilder');
 
insert into devis_ligne (devis_id, produit_id, quantite) values (1, 1, 2);
insert into devis_ligne (devis_id, produit_id, quantite) values (1, 2, 2);
insert into devis_ligne (devis_id, produit_id, quantite) values (1, 3, 4);
insert into devis_ligne (devis_id, produit_id, quantite) values (1, 4, 0);
insert into devis_ligne (devis_id, produit_id, quantite) values (1, 5, 0);
insert into devis_ligne (devis_id, produit_id, quantite) values (1, 6, 2);
insert into devis_ligne (devis_id, produit_id, quantite) values (1, 7, 1);

Et la requête qui passe pas :

Code :

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

1
2
3
4
5
6
7
8
9
select devis.nom, jours.dates, saison.nom, produit.nom, prix.montant, devis_ligne.quantite, prix.montant * devis_ligne.quantite
from Devis
cross join ListeJours(devis.date_debut, devis.date_fin) jours
inner join Devis_Ligne on Devis_Ligne.devis_id = devis.id 
inner join Saison_Date on jours.dates between saison_date.date_debut and saison_date.date_fin
inner join Saison on saison.id = saison_date.saison_id
inner join prix on prix.saison_id = saison_date.saison_id
inner join produit on produit.id = prix.produit_id and produit.id = devis_ligne.produit_id
where Devis.id = 1

Et la requête qui passe, mais qui ne m'avance guère...

Code :

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

1
2
3
4
5
6
7
8
9
select devis.nom, jours.dates, saison.nom, produit.nom, prix.montant, devis_ligne.quantite, prix.montant * devis_ligne.quantite
from Devis
cross join ListeJours('1900-07-20', '1900-08-10') jours
inner join Devis_Ligne on Devis_Ligne.devis_id = devis.id 
inner join Saison_Date on jours.dates between saison_date.date_debut and saison_date.date_fin
inner join Saison on saison.id = saison_date.saison_id
inner join prix on prix.saison_id = saison_date.saison_id
inner join produit on produit.id = prix.produit_id and produit.id = devis_ligne.produit_id
where Devis.id = 1

(Ne vous inquiétez pas pour les dates en 1900, c'est juste que ça change pas d'une année à l'autre, donc j'ai pas envie de m'amuser à historiser les dates de saisons, donc je ramène tout à 1900.

[elsuket]

Bonjour,

En effet c'est l'opérateur CROSS APPLY qu'il faut utiliser dans ce cas là.
L'opérateur CROSS JOIN se "fiche" de ce qui se trouve dans les tables qui interviennent dans la jointure.
Si donc vous avez deux lignes dans la table de gauche et 5 dans la table de droite, vous ne aurez 10 quoiqu'il arrive.

L'opérateur CROSS APPLY filtre les lignes retournées par la fonction table en fonction des paramètres qu'on passe à la fonction table.
C'est d'ailleurs ce qui permet de passer une sous-requête corrélée à cet opérateur.

@++

[StringBuilder]

Merci ! Effectivement, avec CROSS APPLY ça fonctionne parfaitement !