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

Version imprimable

Bonjour,

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

Code:

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:

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.

23/10/2019, 15h12
aieeeuuuuu

bonjour,

vous pouvez créer une fonction table en ligne
23/10/2019, 15h19
Invité

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

Code:

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 ;)
28/10/2019, 10h47
QGY71

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 ?

SOLUTION :

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

Code:

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:

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

A +

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:

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:

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:

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.

30/10/2019, 16h07
SQLpro
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:

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:

1 2 select * from dbo].[F_TESTQUENTIN2] ('TC', 'ASL', 0, '2019/10/07', '2019/10/07')
A +
30/10/2019, 16h18
QGY71

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.
31/10/2019, 14h26
aieeeuuuuu

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