Changes between Version 2 and Version 3 of Recette SQL VueLotOpe

Timestamp:

Nov 28, 2016 4:48:03 PM (8 years ago)

Author:

cedric

Comment:

--

Recette SQL VueLotOpe

@@ -16 +16 @@
   ORDER BY t_operation_ope.ope_date_debut;
 }}}
+
+
+
+{{{
+-- DROP VIEW azti.vue_lot_ope_car;
+
+CREATE OR REPLACE VIEW azti.vue_lot_ope_car AS 
+ SELECT t_operation_ope.ope_identifiant, t_lot_lot.lot_identifiant, t_operation_ope.ope_dic_identifiant, t_lot_lot.lot_lot_identifiant AS lot_pere, t_operation_ope.ope_date_debut, t_operation_ope.ope_date_fin, t_lot_lot.lot_effectif, t_lot_lot.lot_quantite, t_lot_lot.lot_tax_code, t_lot_lot.lot_std_code, tr_taxon_tax.tax_nom_latin, tr_stadedeveloppement_std.std_libelle, tr_devenirlot_dev.dev_code, tr_devenirlot_dev.dev_libelle, tg_parametre_par.par_nom, tj_caracteristiquelot_car.car_par_code, tj_caracteristiquelot_car.car_methode_obtention, tj_caracteristiquelot_car.car_val_identifiant, tj_caracteristiquelot_car.car_valeur_quantitatif, tr_valeurparametrequalitatif_val.val_libelle
+   FROM azti.t_operation_ope
+   JOIN azti.t_lot_lot ON t_lot_lot.lot_ope_identifiant = t_operation_ope.ope_identifiant
+   LEFT JOIN ref.tr_typequantitelot_qte ON tr_typequantitelot_qte.qte_code::text = t_lot_lot.lot_qte_code::text
+   LEFT JOIN ref.tr_devenirlot_dev ON tr_devenirlot_dev.dev_code::text = t_lot_lot.lot_dev_code::text
+   JOIN ref.tr_taxon_tax ON tr_taxon_tax.tax_code::text = t_lot_lot.lot_tax_code::text
+   JOIN ref.tr_stadedeveloppement_std ON tr_stadedeveloppement_std.std_code::text = t_lot_lot.lot_std_code::text
+   JOIN azti.tj_caracteristiquelot_car ON tj_caracteristiquelot_car.car_lot_identifiant = t_lot_lot.lot_identifiant
+   LEFT JOIN ref.tg_parametre_par ON tj_caracteristiquelot_car.car_par_code::text = tg_parametre_par.par_code::text
+   LEFT JOIN ref.tr_parametrequalitatif_qal ON tr_parametrequalitatif_qal.qal_par_code::text = tg_parametre_par.par_code::text
+   LEFT JOIN ref.tr_valeurparametrequalitatif_val ON tj_caracteristiquelot_car.car_val_identifiant = tr_valeurparametrequalitatif_val.val_identifiant
+  ORDER BY t_operation_ope.ope_date_debut;
+
+ALTER TABLE azti.vue_lot_ope_car
+  OWNER TO postgres;
+GRANT ALL ON TABLE azti.vue_lot_ope_car TO postgres;
+GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE azti.vue_lot_ope_car TO azti;
+GRANT SELECT ON TABLE azti.vue_lot_ope_car TO invite;
+
+}}}