Factorisation de calcul dans une requete

Version imprimable

06/08/2009, 17h23
banjo12

Factorisation de calcul dans une requete
Bonjour,
Je cherche a faire un truc du genre :
Code:

1 2 3 4 SELECT (...) as Montant_TVA, (...) as Montant_HT, (Montant_TVA + Montant_HT) as Montant_TTC FROM ...
J'ai lu dans d'autres sujets du forum que l'utilisation d'alias comme dans ces exemples etaient impossibles, car les alias sont affectés après le calcul du résultats.

Je voudrais savoir s'il y avait un autre moyen que les alias pour effectuer les memes choses. Ce qui serait bien pratique pour la lisibilité des requetes et leur maintenance.

Bonjour,

vous pouvez essayer :

Code:

1
2
3
4
5
 
SELECT  Montant_TVA, Montant_HT, 
           (Montant_TVA + Montant_HT) AS Montant_TTC
FROM (SELECT (...) AS Montant_TVA, (...) AS Montant_HT
          FROM ...)

06/08/2009, 19h53
MaitrePylos
Quel est la difficulté ?

Montre nous la structure de la table

parce ce que ceci doit-être ce que tu cherches
Code:

1 2 3 4 5 SELECT champs1 AS montant_tva,champs2 AS montant_ht, (champ1 + champ2) AS montant_ttc FROM taTable
07/08/2009, 08h43
banjo12

La diffilculté vient du fait que champ1 et champ2 ne sont pas des calculs simples mais des imbrications de "CASE WHEN", de "CONVERT", etc...
Ce qui fait que j'arrive à des requêtes illisibles et difficilement maintenables.

icsor, t'as solution me semble un peu plus propre, je vais essayer de m'orienter vers ça !

Merci de votre aide, mais si quelqu'un a encore plus simple je suis preneur !
07/08/2009, 08h56
MaitrePylos

Ben montre nous le requête pour voir si on peut la simplifier !

Voila un exemple :

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
 
SELECT      VEHICULE.vhcod, VEHICULE.vhcateg, VEHICULE.vhptac, MENSUTRA.peimmat, VEHICULE.vhimmat, TRANSPORTEUR.trnom2, TRANSPORTEUR.trcod, 
                        VEHICULE.vhordre, MENSUTRA.penum, MENSUTRA.pecprod, MENSUTRA.penom, MENSUTRA.peprenom, MENSUTRA.pezont, ZONETAR.ztcod, 
                        ZONETAR.ztpxsr, MENSUTRA.pepdsb, MENSUTRA.petare, MENSUTRA.pepdsn, 
(CASE WHEN (MENSUTRA.pepdsb > ((VEHICULE.vhptac + 2) * 100)) THEN ((VEHICULE.vhptac + 2) * 100) - MENSUTRA.petare ELSE  MENSUTRA.pepdsn END) AS PNetpaye, 
(CASE WHEN (VEHICULE.vhcateg = 'CR') THEN ZONETAR.ztpxcr ELSE ZONETAR.ztpxsr END) AS TarifOld, MENSUTRA.peprix AS Tarif, 
(CASE WHEN (MENSUTRA.pepdsb > ((VEHICULE.vhptac + 2) * 100)) THEN (MENSUTRA.pepdsb - (2 + VEHICULE.vhptac) * 100) ELSE  0 END) AS Schge, 
MENSUTRA.pedat, MENSUTRA.peetc, MENSUTRA.peagri, MENSUTRA.peparc, MENSUTRA.petrsp, TRANSPORTEUR.trcp, TRANSPORTEUR.trvil,
 MENSUTRA.petyp, TRANSPORTEUR.trnom, TRANSPORTEUR.tradr1, TRANSPORTEUR.tradr2, TRANSPORTEUR.trsap, PRODUIT.prnom, 
PAYS.pytxtva1, PAYS.pytxtva2, MENSUTRA.pevalid, MENSUTRA.petrans, 
TRANSPORTEUR.trtva, (CASE WHEN ([petyp] = 'ENTREE') THEN trNumBCA ELSE trNumBCE END) AS NumComm,
 
 
(((CASE WHEN (MENSUTRA.pepdsb > ((VEHICULE.vhptac + 2) * 100)) THEN ((VEHICULE.vhptac + 2) * 100) - MENSUTRA.petare ELSE  MENSUTRA.pepdsn END)/100)*
MENSUTRA.peprix) as Montant_HT, 
 
 
((((CASE WHEN (MENSUTRA.pepdsb > ((VEHICULE.vhptac + 2) * 100)) THEN ((VEHICULE.vhptac + 2) * 100) - MENSUTRA.petare ELSE  MENSUTRA.pepdsn END)/100)*
MENSUTRA.peprix)*[pytxtva2]/100) as Montant_TVA,
 
 
((((CASE WHEN (MENSUTRA.pepdsb > ((VEHICULE.vhptac + 2) * 100)) THEN ((VEHICULE.vhptac + 2) * 100) - MENSUTRA.petare ELSE  MENSUTRA.pepdsn END)/100)*
MENSUTRA.peprix)+
(((CASE WHEN (MENSUTRA.pepdsb > ((VEHICULE.vhptac + 2) * 100)) THEN ((VEHICULE.vhptac + 2) * 100) - MENSUTRA.petare ELSE  MENSUTRA.pepdsn END)/100)*
MENSUTRA.peprix)*[pytxtva2]/100) as Montant_TTC
 
 
FROM          (((MENSUTRA LEFT JOIN
                        (VEHICULE LEFT JOIN
                        TRANSPORTEUR ON VEHICULE.vhcod = TRANSPORTEUR.trcod) ON MENSUTRA.peimmat = VEHICULE.vhimmat) LEFT JOIN
                        ZONETAR ON MENSUTRA.pezont = ZONETAR.ztcod) LEFT JOIN
                        PRODUIT ON MENSUTRA.pecprod = PRODUIT.prcod) LEFT JOIN
                        PAYS ON TRANSPORTEUR.trpays = PAYS.pycod
WHERE      (((MENSUTRA.petrsp) <> '00000099' AND (MENSUTRA.petrsp) <> '00000098') AND ((MENSUTRA.pevalid) <> 0));

Pour la petite histoire, je migre un fichier mdb vers un fichier Client/Serveur ADP. Au niveau des états, on ne peut plus utiliser de fonctions d'aggrégation sur des champs calculés, genre Somme(champ1 * champ2) (http://support.microsoft.com/kb/225992). Donc tous ces calculs complexes sont transvasés dans la requete SQL.

En DB2, quelque chose comme ceci est faisable :

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
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
 
WITH TABLE_MAITREPYLOS( vhcod, vhcateg, vhptac,peimmat, vhimmat,trnom2,trcod,vhordre,penum,pecprod, 
 penom, peprenom, pezont, ztcod,ztpxsr, pepdsb, petare, pepdsn,PNetpaye,Tarif,Schge,pedat,peetc, 
 peagri,peparc, petrsp,trcp, trvil, petyp, trnom,tradr1,tradr2,trsap, prnom,pytxtva1,pytxtva2, 
pevalid,petrans,trtva,NumComm, Montant_HT, Montant_TVA, Montant_TTC) AS
(
SELECT
 VEHICULE.vhcod, VEHICULE.vhcateg, VEHICULE.vhptac, MENSUTRA.peimmat, VEHICULE.vhimmat, 
 TRANSPORTEUR.trnom2, TRANSPORTEUR.trcod,VEHICULE.vhordre, MENSUTRA.penum, MENSUTRA.pecprod, 
 MENSUTRA.penom, MENSUTRA.peprenom, MENSUTRA.pezont, ZONETAR.ztcod,
 ZONETAR.ztpxsr, MENSUTRA.pepdsb, MENSUTRA.petare, MENSUTRA.pepdsn, 
(
	CASE 
		WHEN (MENSUTRA.pepdsb > ((VEHICULE.vhptac + 2) * 100)) 
			THEN ((VEHICULE.vhptac + 2) * 100) - MENSUTRA.petare 
			ELSE  MENSUTRA.pepdsn 
	END) AS PNetpaye,
 
(
	CASE 
		WHEN (VEHICULE.vhcateg = 'CR') 
			THEN ZONETAR.ztpxcr 
			ELSE ZONETAR.ztpxsr 
	END) AS TarifOld, MENSUTRA.peprix AS Tarif,
 
(
	CASE 
		WHEN (MENSUTRA.pepdsb > ((VEHICULE.vhptac + 2) * 100)) 
			THEN (MENSUTRA.pepdsb - (2 + VEHICULE.vhptac) * 100) 
			ELSE  0 
	END) AS Schge, 
MENSUTRA.pedat, MENSUTRA.peetc, MENSUTRA.peagri, MENSUTRA.peparc, MENSUTRA.petrsp, 
TRANSPORTEUR.trcp, TRANSPORTEUR.trvil, MENSUTRA.petyp, TRANSPORTEUR.trnom, TRANSPORTEUR.tradr1, 
TRANSPORTEUR.tradr2, TRANSPORTEUR.trsap, PRODUIT.prnom,PAYS.pytxtva1, PAYS.pytxtva2, 
MENSUTRA.pevalid, MENSUTRA.petrans,TRANSPORTEUR.trtva, 
(
	CASE 
		WHEN ([petyp] = 'ENTREE') 
			THEN trNumBCA 
			ELSE trNumBCE 
	END) AS NumComm, 
 
(
	(
		(
			CASE 
				WHEN (MENSUTRA.pepdsb > ((VEHICULE.vhptac + 2) * 100)) 
					THEN ((VEHICULE.vhptac + 2) * 100) - MENSUTRA.petare 
					ELSE  MENSUTRA.pepdsn 
			END)/100)* MENSUTRA.peprix) AS Montant_HT, 
 
 
(
	(
		(
			(
				CASE 
					WHEN (MENSUTRA.pepdsb > ((VEHICULE.vhptac + 2) * 100)) 
						THEN ((VEHICULE.vhptac + 2) * 100) - MENSUTRA.petare 
						ELSE  MENSUTRA.pepdsn 
				END)/100)* MENSUTRA.peprix)*[pytxtva2]/100) AS Montant_TVA, 
 
(
	(
		(
			(
				CASE 
					WHEN (MENSUTRA.pepdsb > ((VEHICULE.vhptac + 2) * 100)) 
						THEN ((VEHICULE.vhptac + 2) * 100) - MENSUTRA.petare 
						ELSE  MENSUTRA.pepdsn END)/100)* MENSUTRA.peprix) +
							(
								(
									(
										CASE 
											WHEN (MENSUTRA.pepdsb > ((VEHICULE.vhptac + 2) * 100)) 
												THEN ((VEHICULE.vhptac + 2) * 100) - MENSUTRA.petare 
												ELSE  MENSUTRA.pepdsn 
										END)/100)* MENSUTRA.peprix)*[pytxtva2]/100) AS Montant_TTC
 
 
FROM
(
	(
		(
			MENSUTRA 
				LEFT JOIN
					(VEHICULE LEFT JOIN TRANSPORTEUR 
						ON VEHICULE.vhcod = TRANSPORTEUR.trcod) 
					ON MENSUTRA.peimmat = VEHICULE.vhimmat) 
				LEFT JOIN ZONETAR 
					ON MENSUTRA.pezont = ZONETAR.ztcod)
			    LEFT JOIN PRODUIT 
					ON MENSUTRA.pecprod = PRODUIT.prcod) 
				LEFT JOIN PAYS 
					ON TRANSPORTEUR.trpays = PAYS.pycod
WHERE (
	(
		(MENSUTRA.petrsp) <> '00000099' 
			AND (MENSUTRA.petrsp) <> '00000098') 
			AND ((MENSUTRA.pevalid) <> 0)
	);
 )
 SELECT vhcod, vhcateg, vhptac,peimmat, vhimmat,trnom2,trcod,vhordre,penum,pecprod, 
 penom, peprenom, pezont, ztcod,ztpxsr, pepdsb, petare, pepdsn,PNetpaye,Tarif,Schge,pedat,peetc, 
 peagri,peparc, petrsp,trcp, trvil, petyp, trnom,tradr1,tradr2,trsap, prnom,pytxtva1,pytxtva2, 
 pevalid,petrans,trtva,NumComm, Montant_HT, Montant_TVA, Montant_TTC,
 (Montant_HT - Montant_TVA) AS Montant_HT_TVA
 
FROM TABLE_MAITREPYLOS

J'imagine que vous êtes sur SQL-SERVER, il faudrais voir ce qu'ils proposent au niveau de la récursivité .

07/08/2009, 10h26
banjo12

Effectivement, je suis sous SQL Server 2005. Pourriez vous m'indiquer ou vous cherchez ces infos.
Personnellement, je trouve que MSDN est trés bien pour la définition des fonctions TRANSACT-SQL, mais j'ai du mal à trouver des méthodes ou les bonnes pratiques pour construire des requetes ...
07/08/2009, 10h40
MaitrePylos

Si je ne m'abuse, la récursivité est apparue dans la norme sql2003, une petite recherche sur Google, m'apprend que dans SQL-Server 2005 , cela est nommé 'Common Table Expressions' ou encore CTE .

Pour mes recherche j'utilise ce forum même, Google et des bouquins dont celui-ci de sqlpro (Frédéric Brouard)
07/08/2009, 11h06
banjo12

ok ok.
Bon je le mets en résolu, il me semble qu'on a fait le tour.
Merci beaucoup des conseils avisés .