Explication union et order by

[guigui69]

Bonjour à tous,

SQL 2000 /windows 2003

Voici ma requete:

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
 
SELECT 
tcPresupuesto.Emp AS "n societe", 
tcVeh.Chasis AS "vin",
tgMarca.Descrip AS "marque", 
 REPLACE(tgModelo.Descrip, ',', '.') AS "modele",
 REPLACE(tgVersion.Descrip, ',', '.') AS "version", 
 REPLACE(tcVeh.Version , ',', '.')  AS "code_version",
'' AS "carrosserie",
 tcVeh.Combustible "Carburant", 
 tcVeh.Puertas AS "Porte",
  CONVERT(varchar,tcVeh.FecPedido,103) AS "Date_Commande", 
  CONVERT(varchar,tcVeh.FecEntradaFisica,103) AS "date_entree_physique",
 CONVERT(varchar,tcPresupuesto.FechaFactura,103) AS "datefacture",
 REPLACE(REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(REPLACE(REPLACE(tcvehOpcion.Descrip, CHAR(13), ' '), CHAR(10), ' '))), ',', ' '),'=',' '),'-',' '),'"','') AS "Libelles_Options",
 LTRIM(RTRIM(tcvehOpcion.Opcion)) AS "code_option",
(case when tcveh.esdemo = 1 then 'VD'
when tcveh.eskm0 = 1 then 'V0km'
else 'VN'
end) AS typevehicule,
 case
 LTRIM(RTRIM(tgCliente.PerJuridica)) WHEN '1' then 'Societe' else 'Particulier' end AS "type_client",
 tcPresupuesto.FinanImporte AS "Mt_financement",
  (COALESCE(tcPresupuesto.Subtotal16,0)) AS "Mt_reprise",
  '' AS "marque_veh_vo",'' AS "modele_veh_vo",
  (COALESCE(tcPresupuesto.Subtotal1, 0)) AS "Prix_vente_depart_HT",
   (COALESCE(tcPresupuesto.Subtotal3, 0)) AS "Option2_HT",
    (COALESCE(tcPresupuesto.Subtotal7, 0) * (-1)) AS "Remise_HT",
	'' AS "transfertdemargevo",
	(COALESCE(tcPresupuesto.Subtotal8, 0)) AS "totalfactureclient_HT"
 
 
 
FROM tcPresupuesto
LEFT OUTER JOIN tgVendedor ON tcPresupuesto.Emp = tgVendedor.Emp AND tcPresupuesto.Vendedor = tgVendedor.Vendedor 
LEFT OUTER JOIN tgCliente ON tcPresupuesto.ClienteFac = tgCliente.Codigo 
LEFT OUTER JOIN tcVeh ON tcPresupuesto.Emp = tcVeh.Emp AND tcPresupuesto.Vehiculo = tcVeh.NumInterno 
LEFT OUTER JOIN tgMarca ON tcPresupuesto.Marca = tgMarca.Marca 
LEFT OUTER JOIN tgModelo ON tcPresupuesto.Modelo = tgModelo.Modelo AND tcPresupuesto.Marca = tgModelo.Marca 
LEFT OUTER JOIN tgVersion ON tcPresupuesto.Marca = tgVersion.Marca AND tcPresupuesto.Modelo = tgVersion.Modelo AND tcPresupuesto.Version = tgVersion.Version 
LEFT OUTER JOIN tcTapiz ON tcPresupuesto.Marca = tcTapiz.Marca AND tcPresupuesto.Tapiz = tcTapiz.Tapiz 
LEFT OUTER JOIN tcColor ON tcPresupuesto.Marca = tcColor.Marca AND tcPresupuesto.Color = tcColor.Color 
LEFT OUTER JOIN tcGarantia ON tcGarantia.Marca = '*' AND tcGarantia.Garantia = tcPresupuesto.Garantia 
LEFT OUTER JOIN tgCategoria ON tcPresupuesto.Categoria = tgCategoria.Categoria 
LEFT OUTER JOIN tcVehOpcion ON tcPresupuesto.Emp = tcVehOpcion.Emp AND tcPresupuesto.Vehiculo = tcVehOpcion.NumInterno AND tcVehOpcion.TipoLinea <> 'A'
LEFT OUTER JOIN tgClienteFac ON tcPresupuesto.Emp = tgClienteFac.Emp AND tcPresupuesto.ClienteFac = tgClienteFac.Codigo 
LEFT OUTER JOIN tgEmpresa ON tcPresupuesto.Emp = tgEmpresa.Emp
WHERE 
( tcPresupuesto.EsVO = 0) AND 
( tcPresupuesto.STATUS <> 50 ) AND
((tcPresupuesto.PasoFasePresupuesto = 9) OR (tcPresupuesto.PasoFaseExpediente = 9) OR (tcPresupuesto.PasoFaseVenta = 1) OR (tcPresupuesto.PasoFaseCierre = 9)) AND
(tcPresupuesto.FechaFactura BETWEEN '07-01-2013 00:00:00.000' AND '07-01-2013 23:59:59.000')
ORDER BY tcvehopcion.tipolinea
 
UNION
 
SELECT 
 
tcVeh.Emp AS "n societe", 
case when tcVeh.Chasis = '' then tcveh.numpedidofab else  tcVeh.Chasis end AS "vin",
tgMarca.Descrip AS "marque", 
 REPLACE(tgModelo.Descrip, ',', '.') AS "modele",
 REPLACE(tgVersion.Descrip, ',', '.') AS "version", 
 REPLACE(tcVeh.Version , ',', '.')  AS "code_version",
'' AS "carrosserie",
 tcVeh.Combustible "Carburant", 
 tcVeh.Puertas AS "Porte",
  CONVERT(varchar,tcVeh.FecPedido,103) AS "Date_Commande", 
  CONVERT(varchar,tcVeh.FecEntradaFisica,103) AS "date_entree_physique",
'' AS "datefacture",
 REPLACE(REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(REPLACE(REPLACE(tcvehOpcion.Descrip, CHAR(13), ' '), CHAR(10), ' '))), ',', ' '),'=',' '),'-',' '),'"','') AS "Libelles_Options",
 LTRIM(RTRIM(tcvehOpcion.Opcion)) AS "code_option",
(case when tcveh.esdemo = 1 then 'VD'
when tcveh.eskm0 = 1 then 'V0km'
else 'VN'
end) AS typevehicule,
 case
 LTRIM(RTRIM(tgCliente.PerJuridica)) WHEN '1' then 'Societe' else 'Particulier' end AS "type_client",
'0' AS "Mt_financement",
'0' AS "Mt_reprise",
  '' AS "marque_veh_vo",'' AS "modele_veh_vo",
tcvehOpcion.VentaPrecio AS "Prix_vente_depart_HT",
'0' AS "Option2_HT",
'0' AS "Remise_HT",
'0' AS "transfertdemargevo",
'0' AS "totalfactureclient_HT"
FROM tcVeh
LEFT OUTER JOIN tgEmpresa ON tcVeh.Emp = tgEmpresa.Emp
LEFT OUTER JOIN TgMarca ON tcveh.Marca = tgMarca.Marca
LEFT OUTER JOIN tgModelo ON tcveh.Marca = tgModelo.Marca AND tcveh.Modelo = tgModelo.Modelo
LEFT OUTER JOIN tgVersion ON tcveh.Marca = tgVersion.Marca AND tcveh.Modelo = tgVersion.Modelo AND tcveh.Version = tgVersion.Version
LEFT OUTER JOIN tgPtoVenta ON tcVeh.Emp = tgPtoVenta.Emp AND tcVeh.PuntoVenta = tgPtoVenta.PuntoVenta 
LEFT OUTER JOIN tccolor ON tcveh.marca = tccolor.marca AND tcveh.color = tccolor.color 
LEFT OUTER JOIN tctapiz ON tcveh.marca = tctapiz.marca AND tcveh.tapiz = tctapiz.tapiz 
LEFT OUTER JOIN tgVendedor ON tcveh.Emp = tgVendedor.Emp AND tcveh.reservaVendedor = tgVendedor.Vendedor 
LEFT OUTER JOIN tgcliente ON tcveh.reservaCliente = tgCliente.Codigo
LEFT OUTER JOIN tcvehopcion ON tcveh.emp = tcvehopcion.emp AND tcveh.numinterno = tcvehopcion.numinterno
LEFT OUTER JOIN tcvehsubstatus ON tcveh.STATUS=tcvehsubstatus.STATUS AND tcveh.substatus=tcvehsubstatus.substatus
LEFT OUTER JOIN tcubicacion ON tcveh.emp = tcubicacion.emp AND tcveh.ubicacion = tcubicacion.ubicacion
LEFT OUTER JOIN tgcategoria ON tcveh.categoria = tgcategoria.categoria
LEFT OUTER JOIN ttCortesiaCategoria ON ttCortesiaCategoria.CortesiaCategoria = tcVeh.CortesiaCategoria
WHERE
tcveh.EsVO = 0 AND 
tcveh.STATUS < 30 AND
tcveh.STATUS > 0 AND 
tcvehsubstatus.substatus IN ('11','21')
AND  (REPLACE(tgModelo.Descrip, ',', '.')='B-MAX')
 
ORDER BY tcvehopcion.tipolinea

Ligne: 53 /54 /109

J'aurai voulu savoir comment on utilise UNION et ORDER BY en meme temps ?

Car j'ai rajouter ORDER BY dans les 2 requetes mais il m'indique "Syntaxe incorrecte vers le mot clé 'UNION'." je n'arrive pas avoir l'élément qui pose probleme .

Merci d'avance pour votre aide

guigui69

[SQLpro]

ORDER BY n'est pas une opération relationnelle mais cosmétique. Elle ne change rien au contenu du résultat. Elle en modifie l'apparence (cosmétique) . Cette clause ne peut donc porter que sur le résultat et non chaque requête intermédiaire, ce qui n'aurait aucun sens !