Création d'une vue avec des sous requêtes paramétrées

**QGY71** · 23/10/2019, 15h54

Bonjour,

-- Requête faite avec SQL opérationnel et paramétrable avec les valeurs entre guillemets

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
select *
from TPMOYENS, TPPOOLM
WHERE TPMOYENS.TRACTEI > 0
AND TPMOYENS.POOLTRC = TPPOOLM.POOLM0C
AND TPMOYENS.ACTIVIC = 'TC'
AND TPMOYENS.POOLTRC = 'ASL'
AND convert(varchar, TPMOYENS.DEBAFFD, 111) <= '2019/07/10'
AND convert(varchar, TPMOYENS.FINAFFD, 111) >= '2019/07/10'
AND NOT EXISTS (SELECT *
	FROM TMVOYENT
	WHERE TPMOYENS.TRACTEI = TMVOYENT.TRACTEI
	AND TMVOYENT.ACTIVIC = 'TC'
	AND (convert(varchar, TMVOYENT.DPACHTD, 111) <= '2019/07/10') 
	AND (convert(varchar, TMVOYENT.FPACHTD, 111) >= '2019/07/10')
			)	
AND NOT EXISTS (SELECT *
			FROM TMINDMOY
			WHERE TMINDMOY.TRACTEI = TPMOYENS.TRACTEI
			AND convert(varchar, TMINDMOY.DEBINDD, 111) <= '2019/07/10'
			AND convert(varchar, TMINDMOY.FININDD, 111) >= '2019/07/10'
			)
AND NOT EXISTS (SELECT *
			FROM TPMOYENS as B
			WHERE TPMOYENS.TRACTEI = B.TRACTEI
			AND convert(varchar, B.DEBAFFD, 111) <= '2019/07/10'
			AND convert(varchar, B.FINAFFD, 111) >= '2019/07/10'
			AND B.CONTRAC = 'T'
			)
ORDER BY POOLTRC, TRACTEA

Nous souhaitons utiliser cette requête dans une vue avec un choix de paramètres. (WHERE)

exemple :

Code :

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

1
2
3
4
5
6
select DISTINCT( id_Tract_Contrat)
from [V_Contrat_Tracteur_non_utilises]
where Activite_Contrat = 'TC'
AND Date_Debut_Contrat <= '2019/10/07'
AND Date_Fin_Contrat >= '2019/10/07'
AND Pool_Tract_Contrat = 'ASL'

Merci par avance.

**aieeeuuuuu** · 23/10/2019, 16h12

bonjour,

vous pouvez créer une fonction table en ligne

Invité · 23/10/2019, 16h19

Même réponse que aieeeuuuuu.
Par contre :

Code :

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

convert(varchar, TPMOYENS.DEBAFFD, 111)

Pourquoi convertir en varchar et pas utilisé des datetime2 ou date ?
Et c'est toujours une bonne pratique de fixer la longueur d'un varchar

**QGY71** · 28/10/2019, 11h47

Bonjour,

Nous avons les contraintes suivantes :

- Utilisation impérative d'une vue (requêteur INSIDE) avec SELECT sur deux dates (par <= et >=) donc ces deux valeurs de dates ne se retrouvent pas dans la vue
- Sous requêtes complémentaires en utilisant ces mêmes dates avec toujours (<= et >=)

Sauf erreur de notre par il n'est pas possible de définir et d'utiliser des paramètres dans une vue : récupération des paramètre depuis le premier SELECT et passage de ceux-ci a la sous requête (via une fonction ?)
Notre problème semble être lié au faite que l'on ne puisse pas récupérer les deux première valeur de date à tester

Quelle solution peut-on envisager ?

**SQLpro** · 29/10/2019, 14h41

SOLUTION :

-- vue paramétrique = UDF table en ligne :

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
CREATE FUNCTION F_T_BLABLA (@DD DATE, @DF DATE)
RETURNS TABLE
AS
RETURN(
SELECT *
from TPMOYENS, TPPOOLM
WHERE TPMOYENS.TRACTEI > 0
AND TPMOYENS.POOLTRC = TPPOOLM.POOLM0C
AND TPMOYENS.ACTIVIC = 'TC'
AND TPMOYENS.POOLTRC = 'ASL'
AND TPMOYENS.DEBAFFD <= @DD
AND TPMOYENS.FINAFFD >= @DF
AND NOT EXISTS (SELECT *
	FROM TMVOYENT
	WHERE TPMOYENS.TRACTEI = TMVOYENT.TRACTEI
	AND TMVOYENT.ACTIVIC = 'TC'
	AND (TMVOYENT.DPACHTD <= @DD) 
	AND (TMVOYENT.FPACHTD >= @DF)
			)	
AND NOT EXISTS (SELECT *
			FROM TMINDMOY
			WHERE TMINDMOY.TRACTEI = TPMOYENS.TRACTEI
			AND TMINDMOY.DEBINDD <= @DD
			AND TMINDMOY.FININDD >= @DF
			)
AND NOT EXISTS (SELECT *
			FROM TPMOYENS as B
			WHERE TPMOYENS.TRACTEI = B.TRACTEI
			AND B.DEBAFFD <= @DD
			AND B.FINAFFD >= @DF
			AND B.CONTRAC = 'T'
			)
)
GO

-- utilisation

Code :

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

1
2
3
SELECT * 
FROM   dbo.F_T_BLABLA('2019/07/10', '2019/07/10')
ORDER  BY POOLTRC, TRACTEA

A +

**QGY71** · 30/10/2019, 12h54

Bonjour,

Merci pour votre aide sur la fonction a mettre en place.
Nous avons fait un essai et nous avons toujours le problème de paramètre de filtre de date que nous sommes contraint de spécifier en dur au niveau de la vue alors que nous voudrions le spécifier uniquement lors de l'utilisation de la vue.

Notre fonction :

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
USE [Transport]
GO
/****** Object:  UserDefinedFunction [dbo].[F_TESTQUENTIN2]   Script Date: 30/10/2019 10:10:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*-------------------------------------------------------------------------------------------------------------------------------------------------
	Description : FONCTION    renvoi le nombre de documents pour une rubrique et une commande                                               
	Table(s)    : TGCDEMET
   
	CREATION :	30/10/2019	QGY
                                                                                                      
--------------------------------------------------------------------------------------------------------------------------------------------------*/
 
ALTER FUNCTION [dbo].[F_TESTQUENTIN2]
(
	@ACT		varchar(10),	  
	@POOL		varchar(10),
	@TRACTEI	bigint,
	@DD			varchar(10),	  
	@DF			varchar(10)
 
)
RETURNS int
AS
BEGIN
	-- Initialise la valeur de retour
	DECLARE @Qt_Voyage int = 0;
 
	-- Compte le nombre de document pour la rubrique passée en paramètre
	-- Prend toutes les rubriques confondues si @GEDAPPI à une valeur null
	-- Ne prend que la rubrique sinon
	SELECT @Qt_Voyage = count (*)
	FROM   TMVOYENT
	WHERE TMVOYENT.TRACTEI = @TRACTEI
	AND TMVOYENT.ACTIVIC = @ACT
	AND convert(varchar, TMVOYENT.DPACHTD, 111) <= @DD
	AND convert(varchar, TMVOYENT.FPACHTD, 111) >= @DF
 
	RETURN @Qt_Voyage;
END;

Notre Vue avec les deux date en dur que nous voulons remplacer par des paramètres, venant du select (requête plus bas) sur la vue:

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
CREATE VIEW [dbo].[V_Contrat_Tracteur_non_utilises]
 
AS
 
SELECT TPMOYENS.ACTIVIC As Activite_Contrat,
		TPMOYENS.POOLTRC As Pool_Tract_Contrat,
		TPMOYENS.DEBAFFD As Date_Debut_Contrat,
		TPMOYENS.FINAFFD As Date_Fin_Contrat,
		TPMOYENS.TRACTEA As Id_Tract,
		TRANSPORT.dbo.F_TESTQUENTIN2(TPMOYENS.ACTIVIC, TPMOYENS.POOLTRC, TPMOYENS.TRACTEI, '2019/07/10', '2019/07/10') As Qt_Voyage
from TPMOYENS, TPPOOLM
WHERE TPMOYENS.TRACTEI > 0
AND TPMOYENS.POOLTRC = TPPOOLM.POOLM0C
 
GO

Notre requête sur notre vue :

Code :

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

1
2
3
4
5
6
7
select *
from V_Contrat_Tracteur_non_utilises
where Activite_Contrat = 'TC'
AND Date_Debut_Contrat <= '2019/10/07'
AND Date_Fin_Contrat >= '2019/10/07'
AND Pool_Tract_Contrat = 'ASL'
AND Qt_Voyage = 0

Merci par avance

Cordialement.

**SQLpro** · 30/10/2019, 17h07

Mais virez moi ces putains de CONVERT bon sang de bonsoir !!!!! Il ne servent qu'a plomber les performances…. Une date est une date !

Code :

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

1
2
	AND convert(varchar, TMVOYENT.DPACHTD, 111) <= @DD
	AND convert(varchar, TMVOYENT.FPACHTD, 111) >= @DF

Une date = une date
et non pas :

je convertis explicitement une date en chaine = on convertit implicitement une date en chaine

Utilisez la fonction table et non la vue !!!!!

Code :

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

1
2
select *
from dbo].[F_TESTQUENTIN2] ('TC', 'ASL', 0, '2019/10/07', '2019/10/07')

A +

**QGY71** · 30/10/2019, 17h18

Nous n'avons pas le choix que de passer par une vue !!

Nous utilisons INSIDE (Logiciel qui collecte et compile nos données pour créer des tableaux de bord avec filtres.) Il ne veut que des vues.

**aieeeuuuuu** · 31/10/2019, 15h26

vous ne pouvez pas non plus appeler de procédures stockées ?

la seule solution (si on peut appeler ça comme ça) que je vois avec cette contrainte, c'est de créer une table contenant toutes les dates susceptible d'être utilisées comme filtre, et de faire une jointure croisée dans la vue.

Avec un peu de chance, l'optimiseur fera bien son job et ça ne devrait pas trop impacter les performances....

A ce propos, évitez les fonctions scalaires multi instruction, préférez les fonctions tables en ligne

Création d'une vue avec des sous requêtes paramétrées

Développement SQL Server

Discussions similaires

Partager

Partager