------------------------------------------------------------------------
-- Vue générale
------------------------------------------------------------------------
GO
CREATE VIEW CAMION_COMPOSITION_V (CamionVIN, CamionNumber
, MoteurNumeroSerie, TransmissionNumeroSerie
, FrontAxleSerialNumber, FrontNumeroSerie
, ForwardAxleSerialNumber, ForwardNumeroSerie
, RearAxleSerialNumber, RearNumeroSerie)
AS
SELECT x.CamionVIN, x.CamionNumber
, COALESCE(MoteurNumeroSerie, '??') AS MoteurNumeroSerie
, COALESCE(TransmissionNumeroSerie, '??') AS TransmissionNumeroSerie
, COALESCE(FrontAxleSerialNumber, '??') AS FrontAxleSerialNumber
, COALESCE(FrontNumeroSerie, '??') AS FrontNumeroSerie
, COALESCE(ForwardAxleSerialNumber, '??') AS ForwardAxleSerialNumber
, COALESCE(ForwardNumeroSerie, '??') AS ForwardNumeroSerie
, COALESCE(RearAxleSerialNumber, '??') AS RearAxleSerialNumber
, COALESCE(RearNumeroSerie, '??') AS RearNumeroSerie
FROM CAMION AS x FULL JOIN CAMION_MOTEUR_V AS y ON x.CamionVIN = y.CamionVIN
FULL JOIN CAMION_TRANSMISSION_V AS z ON x.CamionVIN = z.CamionVIN
FULL JOIN CAMION_FRONT_V AS t ON x.CamionVIN = t.CamionVIN
FULL JOIN CAMION_FORWARD_V AS u ON x.CamionVIN = u.CamionVIN
FULL JOIN CAMION_REAR_V AS v ON x.CamionVIN = v.CamionVIN
;
Go
J'ai remis à plat les bases de données temp et DZINDZIO_TRUCKS_MANAGEMENT_TEMP.