Bonsoir Ordigil,
Suite :
Pour voir les installations des essieux sur les camions :
SELECT CamionVIN, CamionNumber, AxleSerialNumber
, ContactName as Manufacturer
, AxleType
, PositionCode, GAWR
, ComposantInstallationDate as InstallDate, ComposantDesInstallationDate as DesinstallDate
FROM COMPOSANT_AFFECTATION AS x JOIN COMPOSANT AS y on x.ComposantId = y.ComposantId
JOIN AXLE AS z on x.ComposantId = z.ComposantId
JOIN AXLE_POSITION AS t on x.PositionId = t.PositionId
JOIN CAMION AS u on x.LocalisationId = u.CamionId
JOIN CONTACT as v on v.ContactId = y.ComponentManufacturerId
JOIN AXLE_TYPE AS w on w.AxleTypeId = z.AxleTypeId
ORDER BY CamionVIN
,CASE PositionCode
WHEN 'FRGAWR' THEN 1
WHEN '1INTGAWR' THEN 2
WHEN '2INTGAWR' THEN 3
WHEN '3INTGAWR' THEN 4
WHEN '4INTGAWR' THEN 5
WHEN '5INTGAWR' THEN 6
WHEN '6INTGAWR' THEN 7
WHEN 'REARGAWR' THEN 8
WHEN 'TAG' THEN 9
END
Les essieux, encore :-)
- Vue CREATE VIEW AXLE_COMPOSANT_V (rappel, cf. message du 24 janvier)
------------------------------------------------------------------------------
-- Axles (essieux)
------------------------------------------------------------------------------
CREATE VIEW AXLE_COMPOSANT_V (SerialNumber, MfgDate, PurchaseDate, SaleDate
, Manufacturer, Model
, AxleType
, GAWR, AxleGVWR, AxleTorque)
AS
SELECT AxleSerialNumber
, ComposantDateMfg, ComposantDateAchat, ComposantDateVente
, ContactName, ComponentModelName
, AxleType
, GAWR, AxleGVWR, AxleTorque
FROM COMPOSANT AS x
JOIN AXLE AS y ON x.ComposantId = y.ComposantId
JOIN COMPONENT_MODEL AS z ON x.ComponentManufacturerId = z.ComponentManufacturerId
AND x.ComponentModelId = z.ComponentModelId
JOIN CONTACT AS t ON x.ComponentManufacturerId = t.ContactId
JOIN AXLE_TYPE AS u ON y.AxleTypeId = u.AxleTypeId
;
– Création des triggers associés à la vue :
AXLE_COMPOSANT_INSERT_TR, AXLE_COMPOSANT_UPDATE_TR, AXLE_COMPOSANT_DELETE_TR.
-- Création d’essieux (échantillon) :
INSERT INTO AXLE_COMPOSANT_V (SerialNumber, Manufacturer, Model, AxleType, GAWR, PurchaseDate)
SELECT 'essieu 1', 'Brassens Manufacturier', 'Axle Model FR16'
; , 'FRONT', 16000 , '2018-11-16'
INSERT INTO AXLE_COMPOSANT_V (SerialNumber, Manufacturer, Model, AxleType, GAWR, PurchaseDate)
SELECT 'FRONT16156', 'Brassens Manufacturier', 'Axle Model FR16'
, 'FRONT', 16000 , '2018-11-16'
;
INSERT INTO AXLE_COMPOSANT_V (SerialNumber, Manufacturer, Model, AxleType, GAWR, PurchaseDate)
SELECT 'FORWARD23156', 'Brassens Manufacturier', 'Axle Model FWD23'
, 'Forward', 23000 , '2018-11-23'
;
INSERT INTO AXLE_COMPOSANT_V (SerialNumber, PurchaseDate, Manufacturer, Model, AxleType, GAWR)
SELECT 'REAR23156', '2018-11-20', 'Brassens Manufacturier', 'Axle Model REAR23'
, 'rear', 23000
;
INSERT INTO AXLE_COMPOSANT_V (SerialNumber, PurchaseDate, Manufacturer, Model, AxleType, GAWR)
SELECT 'PUSHER8156_1', '2018-11-21', 'Brassens Manufacturier', 'Axle Model PUSHER8'
, 'pusher', 8000
;
INSERT INTO AXLE_COMPOSANT_V (SerialNumber, PurchaseDate, Manufacturer, Model, AxleType, GAWR)
SELECT 'PUSHER8156_2', '2018-11-22', 'Brassens Manufacturier', 'Axle Model PUSHER8'
, 'pusher', 8000
;
INSERT INTO AXLE_COMPOSANT_V (SerialNumber, PurchaseDate, Manufacturer, Model, AxleType, GAWR)
SELECT 'PUSHER8156_3', '2018-11-23', 'Brassens Manufacturier', 'Axle Model PUSHER8'
, 'pusher', 8000
;
INSERT INTO AXLE_COMPOSANT_V (SerialNumber, PurchaseDate, Manufacturer, Model, AxleType, GAWR)
SELECT 'PUSHER8156_4', '2018-11-24', 'Brassens Manufacturier', 'Axle Model PUSHER8'
, 'pusher', 8000
;
INSERT INTO AXLE_COMPOSANT_V (SerialNumber, PurchaseDate, Manufacturer, Model, AxleType, GAWR)
SELECT 'FRONT14426', '2018-11-26', 'Brassens Manufacturier', 'Axle Model FR14'
, 'FRONT', 14000
;
INSERT INTO AXLE_AFFECTATION_CAMION (CamionVIN, SerialNumber, Position)
SELECT 'post#441', '2NDFRONT14441', '6INTGAWR'
;
Update des essieux :
-------------------------------------
-- Un axle dédié au test
-------------------------------------
INSERT INTO AXLE_COMPOSANT_V (SerialNumber, AxleType, Manufacturer, Model)
SELECT 'temp_fsm_01', 'FRONT', 'Brassens Manufacturier', 'Axle Model FR16'
;
----------------------------------------------------------
-- Un update avec des valeurs fantaisistes (mais testé)
---------------------------------------------------------
UPDATE AXLE_COMPOSANT_V
SET SerialNumber = 'temp_fsm_dernier_cri'
, AxleType = 'pusher'
, MfgDate = '2012-01-12'
, PurchaseDate = '2013-01-13'
, SaleDate = '2014-01-14'
, GAWR = 2000
, AxleGVWR = 3000
, AxleTorque = 12
-- , Manufacturer = 'Meritor', Model = 'Axle Model MER52'
, Model = 'Axle Model PUSHER8'
WHERE SerialNumber = 'temp_fsm_01'
;
Partager