vue lot ope

Retour à Recette SQL

CREATE OR REPLACE VIEW migado.vue_lot_ope 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
   FROM migado.t_operation_ope
   JOIN migado.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
  ORDER BY t_operation_ope.ope_date_debut;

-- View: iav.civelle_taille_poids_stade

-- DROP VIEW iav.civelle_taille_poids_stade;

CREATE OR REPLACE VIEW iav.civelle_taille_poids_stade AS 
 WITH poids AS (
         SELECT vue_lot_ope_car_qan.lot_identifiant, vue_lot_ope_car_qan.car_valeur_quantitatif AS poids
           FROM iav.vue_lot_ope_car_qan
          WHERE "overlaps"(vue_lot_ope_car_qan.ope_date_debut, vue_lot_ope_car_qan.ope_date_fin, '1996-08-01'::date::timestamp without time zone, '2016-08-01'::date::timestamp without time zone) AND vue_lot_ope_car_qan.ope_dic_identifiant = 6 AND vue_lot_ope_car_qan.std_libelle::text = 'civelle'::text AND vue_lot_ope_car_qan.lot_effectif = 1::double precision AND upper(vue_lot_ope_car_qan.car_methode_obtention::text) = 'MESURE'::text AND vue_lot_ope_car_qan.car_par_code::text = 'A111'::text AND vue_lot_ope_car_qan.car_valeur_quantitatif IS NOT NULL
        ), taille AS (
         SELECT vue_lot_ope_car_qan.lot_identifiant, vue_lot_ope_car_qan.car_valeur_quantitatif AS taille
           FROM iav.vue_lot_ope_car_qan
          WHERE "overlaps"(vue_lot_ope_car_qan.ope_date_debut, vue_lot_ope_car_qan.ope_date_fin, '1996-08-01'::date::timestamp without time zone, '2016-08-01'::date::timestamp without time zone) AND vue_lot_ope_car_qan.ope_dic_identifiant = 6 AND vue_lot_ope_car_qan.std_libelle::text = 'civelle'::text AND vue_lot_ope_car_qan.lot_effectif = 1::double precision AND upper(vue_lot_ope_car_qan.car_methode_obtention::text) = 'MESURE'::text AND vue_lot_ope_car_qan.car_par_code::text = '1786'::text AND vue_lot_ope_car_qan.car_valeur_quantitatif IS NOT NULL
        ), stade AS (
         SELECT vue_ope_lot_ech_parqual.lot_identifiant, vue_ope_lot_ech_parqual.val_libelle AS stade
           FROM iav.vue_ope_lot_ech_parqual
          WHERE "overlaps"(vue_ope_lot_ech_parqual.ope_date_debut, vue_ope_lot_ech_parqual.ope_date_fin, '1996-08-01'::date::timestamp without time zone, '2016-08-01'::date::timestamp without time zone) AND vue_ope_lot_ech_parqual.ope_dic_identifiant = 6 AND vue_ope_lot_ech_parqual.std_libelle::text = 'civelle'::text AND vue_ope_lot_ech_parqual.lot_effectif = 1::double precision AND upper(vue_ope_lot_ech_parqual.car_methode_obtention::text) = 'MESURE'::text AND vue_ope_lot_ech_parqual.car_par_code::text = '1791'::text
        ), joined_query AS (
         SELECT 
                CASE
                    WHEN poids.lot_identifiant IS NOT NULL THEN poids.lot_identifiant
                    WHEN poids.lot_identifiant IS NULL AND stade.lot_identifiant IS NULL THEN taille.lot_identifiant
                    ELSE NULL::integer
                END AS lot_identifiant, taille.taille, poids.poids, stade.stade
           FROM poids
      FULL JOIN taille ON poids.lot_identifiant = taille.lot_identifiant
   FULL JOIN stade ON stade.lot_identifiant = poids.lot_identifiant
        )
 SELECT t_operation_ope.ope_identifiant, t_lot_lot.lot_identifiant, t_operation_ope.ope_date_debut, t_operation_ope.ope_operateur, joined_query.taille, joined_query.poids, joined_query.stade
   FROM joined_query
   JOIN iav.t_lot_lot ON joined_query.lot_identifiant = t_lot_lot.lot_identifiant
   JOIN iav.t_operation_ope ON t_lot_lot.lot_ope_identifiant = t_operation_ope.ope_identifiant
  ORDER BY t_operation_ope.ope_date_debut;

ALTER TABLE iav.civelle_taille_poids_stade
  OWNER TO postgres;
GRANT ALL ON TABLE iav.civelle_taille_poids_stade TO postgres;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE iav.civelle_taille_poids_stade TO iav;


-- View: iav.vue_lot_ope_car

-- DROP VIEW iav.vue_lot_ope_car;

CREATE OR REPLACE VIEW iav.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 iav.t_operation_ope
   JOIN iav.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 iav.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 iav.vue_lot_ope_car
  OWNER TO postgres;
GRANT ALL ON TABLE iav.vue_lot_ope_car TO postgres;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE iav.vue_lot_ope_car TO iav;
GRANT SELECT ON TABLE iav.vue_lot_ope_car TO invite;

-- View: iav.vue_lot_ope_car_qan

-- DROP VIEW iav.vue_lot_ope_car_qan;

CREATE OR REPLACE VIEW iav.vue_lot_ope_car_qan 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, 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 iav.t_operation_ope
   JOIN iav.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 iav.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_parametrequantitatif_qan ON tr_parametrequantitatif_qan.qan_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 iav.vue_lot_ope_car_qan
  OWNER TO postgres;
GRANT ALL ON TABLE iav.vue_lot_ope_car_qan TO postgres;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE iav.vue_lot_ope_car_qan TO iav;
GRANT SELECT ON TABLE iav.vue_lot_ope_car_qan TO invite;

-- View: iav.vue_ope_lot_ech_parqual

-- DROP VIEW iav.vue_ope_lot_ech_parqual;

CREATE OR REPLACE VIEW iav.vue_ope_lot_ech_parqual AS 
 SELECT t_operation_ope.ope_identifiant, t_operation_ope.ope_dic_identifiant, t_operation_ope.ope_date_debut, t_operation_ope.ope_date_fin, lot.lot_identifiant, lot.lot_methode_obtention, lot.lot_effectif, lot.lot_quantite, lot.lot_tax_code, lot.lot_std_code, tr_taxon_tax.tax_nom_latin, tr_stadedeveloppement_std.std_libelle, dev.dev_code, dev.dev_libelle, par.par_nom, car.car_par_code, car.car_methode_obtention, car.car_val_identifiant, val.val_libelle, lot.lot_lot_identifiant AS lot_pere, lot_pere.lot_effectif AS lot_pere_effectif, lot_pere.lot_quantite AS lot_pere_quantite, dev_pere.dev_code AS lot_pere_dev_code, dev_pere.dev_libelle AS lot_pere_dev_libelle, parqual.par_nom AS lot_pere_par_nom, parqual.car_par_code AS lot_pere_par_code, parqual.car_methode_obtention AS lot_pere_car_methode_obtention, parqual.car_val_identifiant AS lot_pere_val_identifiant, parqual.val_libelle AS lot_pere_val_libelle
   FROM iav.t_operation_ope
   JOIN iav.t_lot_lot lot ON lot.lot_ope_identifiant = t_operation_ope.ope_identifiant
   LEFT JOIN ref.tr_typequantitelot_qte qte ON qte.qte_code::text = lot.lot_qte_code::text
   LEFT JOIN ref.tr_devenirlot_dev dev ON dev.dev_code::text = lot.lot_dev_code::text
   JOIN ref.tr_taxon_tax ON tr_taxon_tax.tax_code::text = lot.lot_tax_code::text
   JOIN ref.tr_stadedeveloppement_std ON tr_stadedeveloppement_std.std_code::text = lot.lot_std_code::text
   JOIN iav.tj_caracteristiquelot_car car ON car.car_lot_identifiant = lot.lot_identifiant
   LEFT JOIN ref.tg_parametre_par par ON car.car_par_code::text = par.par_code::text
   JOIN ref.tr_parametrequalitatif_qal qal ON qal.qal_par_code::text = par.par_code::text
   LEFT JOIN ref.tr_valeurparametrequalitatif_val val ON car.car_val_identifiant = val.val_identifiant
   LEFT JOIN iav.t_lot_lot lot_pere ON lot_pere.lot_identifiant = lot.lot_lot_identifiant
   LEFT JOIN ref.tr_typequantitelot_qte qte_pere ON qte_pere.qte_code::text = lot_pere.lot_qte_code::text
   LEFT JOIN ref.tr_devenirlot_dev dev_pere ON dev_pere.dev_code::text = lot_pere.lot_dev_code::text
   LEFT JOIN ( SELECT car_pere.car_lot_identifiant, car_pere.car_par_code, car_pere.car_methode_obtention, car_pere.car_val_identifiant, car_pere.car_valeur_quantitatif, car_pere.car_precision, car_pere.car_commentaires, par_pere.par_code, par_pere.par_nom, par_pere.par_unite, par_pere.par_nature, par_pere.par_definition, qal_pere.qal_par_code, qal_pere.qal_valeurs_possibles, val_pere.val_identifiant, val_pere.val_qal_code, val_pere.val_rang, val_pere.val_libelle
   FROM iav.tj_caracteristiquelot_car car_pere
   LEFT JOIN ref.tg_parametre_par par_pere ON car_pere.car_par_code::text = par_pere.par_code::text
   JOIN ref.tr_parametrequalitatif_qal qal_pere ON qal_pere.qal_par_code::text = par_pere.par_code::text
   LEFT JOIN ref.tr_valeurparametrequalitatif_val val_pere ON car_pere.car_val_identifiant = val_pere.val_identifiant) parqual ON parqual.car_lot_identifiant = lot_pere.lot_identifiant;

ALTER TABLE iav.vue_ope_lot_ech_parqual
  OWNER TO postgres;
GRANT ALL ON TABLE iav.vue_ope_lot_ech_parqual TO postgres;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE iav.vue_ope_lot_ech_parqual TO iav;
GRANT SELECT ON TABLE iav.vue_ope_lot_ech_parqual TO invite;
-- View: iav.vue_ope_lot_ech_parquan

-- DROP VIEW iav.vue_ope_lot_ech_parquan;

CREATE OR REPLACE VIEW iav.vue_ope_lot_ech_parquan AS 
 SELECT t_operation_ope.ope_identifiant, t_operation_ope.ope_dic_identifiant, t_operation_ope.ope_date_debut, t_operation_ope.ope_date_fin, lot.lot_identifiant, lot.lot_methode_obtention, lot.lot_effectif, lot.lot_quantite, lot.lot_tax_code, lot.lot_std_code, tr_taxon_tax.tax_nom_latin, tr_stadedeveloppement_std.std_libelle, dev.dev_code, dev.dev_libelle, par.par_nom, car.car_par_code, car.car_methode_obtention, car.car_valeur_quantitatif, lot.lot_lot_identifiant AS lot_pere, lot_pere.lot_effectif AS lot_pere_effectif, lot_pere.lot_quantite AS lot_pere_quantite, dev_pere.dev_code AS lot_pere_dev_code, dev_pere.dev_libelle AS lot_pere_dev_libelle, parqual.par_nom AS lot_pere_par_nom, parqual.car_par_code AS lot_pere_par_code, parqual.car_methode_obtention AS lot_pere_car_methode_obtention, parqual.car_val_identifiant AS lot_pere_val_identifiant, parqual.val_libelle AS lot_pere_val_libelle
   FROM iav.t_operation_ope
   JOIN iav.t_lot_lot lot ON lot.lot_ope_identifiant = t_operation_ope.ope_identifiant
   LEFT JOIN ref.tr_typequantitelot_qte qte ON qte.qte_code::text = lot.lot_qte_code::text
   LEFT JOIN ref.tr_devenirlot_dev dev ON dev.dev_code::text = lot.lot_dev_code::text
   JOIN ref.tr_taxon_tax ON tr_taxon_tax.tax_code::text = lot.lot_tax_code::text
   JOIN ref.tr_stadedeveloppement_std ON tr_stadedeveloppement_std.std_code::text = lot.lot_std_code::text
   JOIN iav.tj_caracteristiquelot_car car ON car.car_lot_identifiant = lot.lot_identifiant
   LEFT JOIN ref.tg_parametre_par par ON car.car_par_code::text = par.par_code::text
   JOIN ref.tr_parametrequantitatif_qan qan ON qan.qan_par_code::text = par.par_code::text
   LEFT JOIN iav.t_lot_lot lot_pere ON lot_pere.lot_identifiant = lot.lot_lot_identifiant
   LEFT JOIN ref.tr_typequantitelot_qte qte_pere ON qte_pere.qte_code::text = lot_pere.lot_qte_code::text
   LEFT JOIN ref.tr_devenirlot_dev dev_pere ON dev_pere.dev_code::text = lot_pere.lot_dev_code::text
   LEFT JOIN ( SELECT car_pere.car_lot_identifiant, car_pere.car_par_code, car_pere.car_methode_obtention, car_pere.car_val_identifiant, car_pere.car_valeur_quantitatif, car_pere.car_precision, car_pere.car_commentaires, par_pere.par_code, par_pere.par_nom, par_pere.par_unite, par_pere.par_nature, par_pere.par_definition, qal_pere.qal_par_code, qal_pere.qal_valeurs_possibles, val_pere.val_identifiant, val_pere.val_qal_code, val_pere.val_rang, val_pere.val_libelle
   FROM iav.tj_caracteristiquelot_car car_pere
   LEFT JOIN ref.tg_parametre_par par_pere ON car_pere.car_par_code::text = par_pere.par_code::text
   JOIN ref.tr_parametrequalitatif_qal qal_pere ON qal_pere.qal_par_code::text = par_pere.par_code::text
   LEFT JOIN ref.tr_valeurparametrequalitatif_val val_pere ON car_pere.car_val_identifiant = val_pere.val_identifiant) parqual ON parqual.car_lot_identifiant = lot_pere.lot_identifiant;

ALTER TABLE iav.vue_ope_lot_ech_parquan
  OWNER TO postgres;
GRANT ALL ON TABLE iav.vue_ope_lot_ech_parquan TO postgres;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE iav.vue_ope_lot_ech_parquan TO iav;
GRANT SELECT ON TABLE iav.vue_ope_lot_ech_parquan TO invite;