INSERT INTO CAMION_LOCALISATION_V (VIN, Immat, DateAchat, Manufacturer, Model, Number)
SELECT 'vin01', 'immat01', '2012-07-01', 'Leclerc', 'Modèle Bozo', '314' --, 'fraise'
;
INSERT INTO CAMION_LOCALISATION_V (VIN, Immat, DateAchat, Manufacturer, Model, Number, Color, Note)
SELECT 'vin02', 'immat02', '2015-07-05', 'Leclerc', 'Modèle Mac Pherson', '324' , 'fraise', 'Mon beau camion à moi'
;
INSERT INTO LOCAL_LOCALISATION_V (LocalCode, LocalNom)
SELECT 'loc01', 'Entrepôt Tartempion' ;
INSERT INTO CAMION_LOCALISATION_V (VIN, Immat, DateAchat, Manufacturer, Model, Number, Color, Note)
SELECT 'vin03', 'immat03', '2016-04-01', 'Leclerc', 'Petit Pierre', '330' , 'jaune', 'difficile à vendre !'
;
INSERT INTO CAMION_LOCALISATION_V (VIN, DateAchat, Manufacturer, Model, Number)
SELECT 'vin04', '2016-04-01', 'Leclerc', 'Le train du Nord', '331'
;
UPDATE LOCAL_LOCALISATION_V
SET LocalNom = 'Ancien entrepôt Tartempion', Note = 'Ce fut dur...'
WHERE LocalCode = 'loc01' ;
INSERT INTO LOCAL_LOCALISATION_V (LocalCode, LocalNom)
SELECT 'loc02', 'Vendu' ;
INSERT INTO CAMION_LOCALISATION_V (VIN, Immat, DateAchat, Manufacturer, Model, Number, Note)
SELECT 'C1', 'immatC1', '2010-09-01', 'Brassens', 'l''Auvergnat', '351', 'Ça roule !'
;
INSERT INTO CAMION_LOCALISATION_V (VIN, Immat, DateAchat, Manufacturer, Model, Number, Color)
SELECT 'C2', 'immatC2', '2010-12-06', 'Brassens', 'Marquise', '352', 'Bleu'
;
UPDATE CAMION_LOCALISATION_V
SET Color = 'framboise', Note = 'note 41'
WHERE VIN = 'vin01' ;
UPDATE LOCAL_LOCALISATION_V
SET LocalNom = 'Ancien entrepôt Tartempion', Note = 'Ce fut dur...'
WHERE LocalCode = 'loc01' ;
--------------------------------------
-- on ne supprime pas les camions
-- Le delete sera inopérant !
-------------------------------------
DELETE FROM CAMION_LOCALISATION_V ;
--*/
SELECT '' as CAMION, * FROM CAMION ;
SELECT '' as LOCAL, * FROM LOCAL ;
select '' as localisation, * from LOCALISATION
SELECT '' as CAMION_LOCALISATION_V, * FROM CAMION_LOCALISATION_V ;
SELECT '' AS LOCAL_LOCALISATION_V, * FROM LOCAL_LOCALISATION_V ;
SELECT '' as vin01a, VIN, Immat, DateAchat, DateSold
, DateMfg, Manufacturer, Model, Number, Color, Note
FROM CAMION_LOCALISATION_V
WHERE VIN = 'vin01' ;
SELECT '' AS vin01b, VIN, Immat, DateAchat, (CASE DateSold WHEN '9999-12-31' THEN '' END) AS DateSold
, (CASE DateMfg WHEN '9999-12-31' THEN '' END) AS DateMfg, Manufacturer, Model, Number, Color, Note
FROM CAMION_LOCALISATION_V
WHERE VIN = 'vin01'
;
------------------------------------------
-- suppression numéro du camion (Number)
------------------------------------------
UPDATE CAMION_LOCALISATION_V SET Number = '' WHERE VIN = 'vin01' ;
SELECT '' as vin01c, * from camion ;
SELECT '' AS vin01d, VIN, Immat, DateAchat, (CASE DateSold WHEN '9999-12-31' THEN '' END) AS DateSold
, (CASE DateMfg WHEN '9999-12-31' THEN '' END) AS DateMfg, Manufacturer, Model, Number, Color, Note
FROM CAMION_LOCALISATION_V
WHERE VIN = 'vin01'
;
--------------------------------------------------------------------
-- tentative de doublon pour Immat
--------------------------------------------------------------------
INSERT INTO CAMION_LOCALISATION_V (VIN, Immat, DateAchat, Manufacturer, Model, Number)
SELECT 'vin05', 'immat01', '2013-07-01', 'Leclerk', 'Modèle Bozo', '314' --, 'fraise' --, 'immat en double'
;
--------------------------------------------------------------
-- création sans immat, ça doit passer (consigné dans Note)
--------------------------------------------------------------
INSERT INTO CAMION_LOCALISATION_V (VIN, Immat, DateAchat, Manufacturer, Model, Number, Note)
SELECT 'vin06', '', '2013-07-01', 'Leclerk', 'Modèle Bozo', '314a', 'Pas d''immat'
;
INSERT INTO CAMION_LOCALISATION_V (VIN, DateAchat, Manufacturer, Model, Number, Note)
SELECT 'vin07', '2013-07-01', 'Leclerk', 'Modèle Bozo', '314b', 'Pas d''immat'
;
------------------------------------------------------
--on efface l'immat, ça doit passer !
------------------------------------------------------
UPDATE CAMION_LOCALISATION_V SET Immat = '' WHERE VIN = 'vin01' ;
SELECT '' AS vin01e, VIN, Immat, DateAchat, (CASE DateSold WHEN '9999-12-31' THEN '' END) AS DateSold
, (CASE DateMfg WHEN '9999-12-31' THEN '' END) AS DateMfg, Manufacturer, Model, Number, Color, Note
FROM CAMION_LOCALISATION_V
WHERE VIN = 'vin01'
-----------------------------------------------------------------------------------
--on tente de doublonner une immat existante, ça ne doit pas passer !
----------------------------------------------------------------------------------
UPDATE CAMION_LOCALISATION_V SET Immat = 'Immat03', Note = 'tentative de doublonnage' WHERE VIN = 'vin01' ;
SELECT '' AS vin01f, VIN, Immat, DateAchat, (CASE DateSold WHEN '9999-12-31' THEN '' END) AS DateSold
, (CASE DateMfg WHEN '9999-12-31' THEN '' END) AS DateMfg, Manufacturer, Model, Number, Color, Note
FROM CAMION_LOCALISATION_V
--WHERE VIN = 'vin01'
;
--------------------------------------------------------------------
-- tentatives d'effacement et de doublonnage pour Number
--------------------------------------------------------------------
UPDATE CAMION_LOCALISATION_V SET Number = '', Note ='Effacer Number, ça doit passer' WHERE VIN = 'vin06' ;
UPDATE CAMION_LOCALISATION_V SET Number = '', Note ='Effacer Number, ça doit passer' WHERE VIN = 'vin07' ;
UPDATE CAMION_LOCALISATION_V SET Number = '324' WHERE VIN = 'vin01' ;
SELECT '' AS vin01k, VIN, Immat, DateAchat, (CASE DateSold WHEN '9999-12-31' THEN '' END) AS DateSold
, (CASE DateMfg WHEN '9999-12-31' THEN '' END) AS DateMfg, Manufacturer, Model, Number, Color, Note
FROM CAMION_LOCALISATION_V
--WHERE VIN = 'vin01'
;
-------------------------------------------------------------------
--vérif si pas de colonne oubliée en update
-------------------------------------------------------------------
UPDATE CAMION_LOCALISATION_V SET Manufacturer = 'Le gars Leclerc', Model = 'Le modèle Bozo revu'
, USDOT = 'dot1', ICCMC = 'iccmc1'
, GVWR = 80000, FRGAWR = 12500, [2NDGVWR] = 22000, [3RDGVWR] = 23000
WHERE VIN = 'vin01' ;
SELECT '' AS vin01m, VIN, Immat, DateAchat, (CASE DateSold WHEN '9999-12-31' THEN '' END) AS DateSold
, (CASE DateMfg WHEN '9999-12-31' THEN '' END) AS DateMfg, Manufacturer, Model, Number, Color
, USDOT, ICCMC, GVWR, FRGAWR, [2NDGVWR], [3RDGVWR], Note
FROM CAMION_LOCALISATION_V
-- WHERE VIN = 'vin01'
;
A vous de tester plus exhaustivement...
Partager