Changes between Version 3 and Version 4 of Recette SQL VueLotOpe


Ignore:
Timestamp:
Nov 28, 2016 5:13:51 PM (8 years ago)
Author:
cedric
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • Recette SQL VueLotOpe

    v3 v4  
    2020 
    2121{{{ 
    22 -- DROP VIEW azti.vue_lot_ope_car; 
     22-- View: iav.civelle_taille_poids_stade 
    2323 
    24 CREATE OR REPLACE VIEW azti.vue_lot_ope_car AS  
     24-- DROP VIEW iav.civelle_taille_poids_stade; 
     25 
     26CREATE OR REPLACE VIEW iav.civelle_taille_poids_stade AS  
     27 WITH poids AS ( 
     28         SELECT vue_lot_ope_car_qan.lot_identifiant, vue_lot_ope_car_qan.car_valeur_quantitatif AS poids 
     29           FROM iav.vue_lot_ope_car_qan 
     30          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 
     31        ), taille AS ( 
     32         SELECT vue_lot_ope_car_qan.lot_identifiant, vue_lot_ope_car_qan.car_valeur_quantitatif AS taille 
     33           FROM iav.vue_lot_ope_car_qan 
     34          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 
     35        ), stade AS ( 
     36         SELECT vue_ope_lot_ech_parqual.lot_identifiant, vue_ope_lot_ech_parqual.val_libelle AS stade 
     37           FROM iav.vue_ope_lot_ech_parqual 
     38          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 
     39        ), joined_query AS ( 
     40         SELECT  
     41                CASE 
     42                    WHEN poids.lot_identifiant IS NOT NULL THEN poids.lot_identifiant 
     43                    WHEN poids.lot_identifiant IS NULL AND stade.lot_identifiant IS NULL THEN taille.lot_identifiant 
     44                    ELSE NULL::integer 
     45                END AS lot_identifiant, taille.taille, poids.poids, stade.stade 
     46           FROM poids 
     47      FULL JOIN taille ON poids.lot_identifiant = taille.lot_identifiant 
     48   FULL JOIN stade ON stade.lot_identifiant = poids.lot_identifiant 
     49        ) 
     50 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 
     51   FROM joined_query 
     52   JOIN iav.t_lot_lot ON joined_query.lot_identifiant = t_lot_lot.lot_identifiant 
     53   JOIN iav.t_operation_ope ON t_lot_lot.lot_ope_identifiant = t_operation_ope.ope_identifiant 
     54  ORDER BY t_operation_ope.ope_date_debut; 
     55 
     56ALTER TABLE iav.civelle_taille_poids_stade 
     57  OWNER TO postgres; 
     58GRANT ALL ON TABLE iav.civelle_taille_poids_stade TO postgres; 
     59GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE iav.civelle_taille_poids_stade TO iav; 
     60 
     61 
     62-- View: iav.vue_lot_ope_car 
     63 
     64-- DROP VIEW iav.vue_lot_ope_car; 
     65 
     66CREATE OR REPLACE VIEW iav.vue_lot_ope_car AS  
    2567 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 
    26    FROM azti.t_operation_ope 
    27    JOIN azti.t_lot_lot ON t_lot_lot.lot_ope_identifiant = t_operation_ope.ope_identifiant 
     68   FROM iav.t_operation_ope 
     69   JOIN iav.t_lot_lot ON t_lot_lot.lot_ope_identifiant = t_operation_ope.ope_identifiant 
    2870   LEFT JOIN ref.tr_typequantitelot_qte ON tr_typequantitelot_qte.qte_code::text = t_lot_lot.lot_qte_code::text 
    2971   LEFT JOIN ref.tr_devenirlot_dev ON tr_devenirlot_dev.dev_code::text = t_lot_lot.lot_dev_code::text 
    3072   JOIN ref.tr_taxon_tax ON tr_taxon_tax.tax_code::text = t_lot_lot.lot_tax_code::text 
    3173   JOIN ref.tr_stadedeveloppement_std ON tr_stadedeveloppement_std.std_code::text = t_lot_lot.lot_std_code::text 
    32    JOIN azti.tj_caracteristiquelot_car ON tj_caracteristiquelot_car.car_lot_identifiant = t_lot_lot.lot_identifiant 
     74   JOIN iav.tj_caracteristiquelot_car ON tj_caracteristiquelot_car.car_lot_identifiant = t_lot_lot.lot_identifiant 
    3375   LEFT JOIN ref.tg_parametre_par ON tj_caracteristiquelot_car.car_par_code::text = tg_parametre_par.par_code::text 
    3476   LEFT JOIN ref.tr_parametrequalitatif_qal ON tr_parametrequalitatif_qal.qal_par_code::text = tg_parametre_par.par_code::text 
    … …  
    3678  ORDER BY t_operation_ope.ope_date_debut; 
    3779 
    38 ALTER TABLE azti.vue_lot_ope_car 
     80ALTER TABLE iav.vue_lot_ope_car 
    3981  OWNER TO postgres; 
    40 GRANT ALL ON TABLE azti.vue_lot_ope_car TO postgres; 
    41 GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE azti.vue_lot_ope_car TO azti; 
    42 GRANT SELECT ON TABLE azti.vue_lot_ope_car TO invite; 
     82GRANT ALL ON TABLE iav.vue_lot_ope_car TO postgres; 
     83GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE iav.vue_lot_ope_car TO iav; 
     84GRANT SELECT ON TABLE iav.vue_lot_ope_car TO invite; 
     85 
     86-- View: iav.vue_lot_ope_car_qan 
     87 
     88-- DROP VIEW iav.vue_lot_ope_car_qan; 
     89 
     90CREATE OR REPLACE VIEW iav.vue_lot_ope_car_qan AS  
     91 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 
     92   FROM iav.t_operation_ope 
     93   JOIN iav.t_lot_lot ON t_lot_lot.lot_ope_identifiant = t_operation_ope.ope_identifiant 
     94   LEFT JOIN ref.tr_typequantitelot_qte ON tr_typequantitelot_qte.qte_code::text = t_lot_lot.lot_qte_code::text 
     95   LEFT JOIN ref.tr_devenirlot_dev ON tr_devenirlot_dev.dev_code::text = t_lot_lot.lot_dev_code::text 
     96   JOIN ref.tr_taxon_tax ON tr_taxon_tax.tax_code::text = t_lot_lot.lot_tax_code::text 
     97   JOIN ref.tr_stadedeveloppement_std ON tr_stadedeveloppement_std.std_code::text = t_lot_lot.lot_std_code::text 
     98   JOIN iav.tj_caracteristiquelot_car ON tj_caracteristiquelot_car.car_lot_identifiant = t_lot_lot.lot_identifiant 
     99   LEFT JOIN ref.tg_parametre_par ON tj_caracteristiquelot_car.car_par_code::text = tg_parametre_par.par_code::text 
     100   LEFT JOIN ref.tr_parametrequantitatif_qan ON tr_parametrequantitatif_qan.qan_par_code::text = tg_parametre_par.par_code::text 
     101   LEFT JOIN ref.tr_valeurparametrequalitatif_val ON tj_caracteristiquelot_car.car_val_identifiant = tr_valeurparametrequalitatif_val.val_identifiant 
     102  ORDER BY t_operation_ope.ope_date_debut; 
     103 
     104ALTER TABLE iav.vue_lot_ope_car_qan 
     105  OWNER TO postgres; 
     106GRANT ALL ON TABLE iav.vue_lot_ope_car_qan TO postgres; 
     107GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE iav.vue_lot_ope_car_qan TO iav; 
     108GRANT SELECT ON TABLE iav.vue_lot_ope_car_qan TO invite; 
     109 
     110-- View: iav.vue_ope_lot_ech_parqual 
     111 
     112-- DROP VIEW iav.vue_ope_lot_ech_parqual; 
     113 
     114CREATE OR REPLACE VIEW iav.vue_ope_lot_ech_parqual AS  
     115 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 
     116   FROM iav.t_operation_ope 
     117   JOIN iav.t_lot_lot lot ON lot.lot_ope_identifiant = t_operation_ope.ope_identifiant 
     118   LEFT JOIN ref.tr_typequantitelot_qte qte ON qte.qte_code::text = lot.lot_qte_code::text 
     119   LEFT JOIN ref.tr_devenirlot_dev dev ON dev.dev_code::text = lot.lot_dev_code::text 
     120   JOIN ref.tr_taxon_tax ON tr_taxon_tax.tax_code::text = lot.lot_tax_code::text 
     121   JOIN ref.tr_stadedeveloppement_std ON tr_stadedeveloppement_std.std_code::text = lot.lot_std_code::text 
     122   JOIN iav.tj_caracteristiquelot_car car ON car.car_lot_identifiant = lot.lot_identifiant 
     123   LEFT JOIN ref.tg_parametre_par par ON car.car_par_code::text = par.par_code::text 
     124   JOIN ref.tr_parametrequalitatif_qal qal ON qal.qal_par_code::text = par.par_code::text 
     125   LEFT JOIN ref.tr_valeurparametrequalitatif_val val ON car.car_val_identifiant = val.val_identifiant 
     126   LEFT JOIN iav.t_lot_lot lot_pere ON lot_pere.lot_identifiant = lot.lot_lot_identifiant 
     127   LEFT JOIN ref.tr_typequantitelot_qte qte_pere ON qte_pere.qte_code::text = lot_pere.lot_qte_code::text 
     128   LEFT JOIN ref.tr_devenirlot_dev dev_pere ON dev_pere.dev_code::text = lot_pere.lot_dev_code::text 
     129   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 
     130   FROM iav.tj_caracteristiquelot_car car_pere 
     131   LEFT JOIN ref.tg_parametre_par par_pere ON car_pere.car_par_code::text = par_pere.par_code::text 
     132   JOIN ref.tr_parametrequalitatif_qal qal_pere ON qal_pere.qal_par_code::text = par_pere.par_code::text 
     133   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; 
     134 
     135ALTER TABLE iav.vue_ope_lot_ech_parqual 
     136  OWNER TO postgres; 
     137GRANT ALL ON TABLE iav.vue_ope_lot_ech_parqual TO postgres; 
     138GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE iav.vue_ope_lot_ech_parqual TO iav; 
     139GRANT SELECT ON TABLE iav.vue_ope_lot_ech_parqual TO invite; 
     140-- View: iav.vue_ope_lot_ech_parquan 
     141 
     142-- DROP VIEW iav.vue_ope_lot_ech_parquan; 
     143 
     144CREATE OR REPLACE VIEW iav.vue_ope_lot_ech_parquan AS  
     145 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 
     146   FROM iav.t_operation_ope 
     147   JOIN iav.t_lot_lot lot ON lot.lot_ope_identifiant = t_operation_ope.ope_identifiant 
     148   LEFT JOIN ref.tr_typequantitelot_qte qte ON qte.qte_code::text = lot.lot_qte_code::text 
     149   LEFT JOIN ref.tr_devenirlot_dev dev ON dev.dev_code::text = lot.lot_dev_code::text 
     150   JOIN ref.tr_taxon_tax ON tr_taxon_tax.tax_code::text = lot.lot_tax_code::text 
     151   JOIN ref.tr_stadedeveloppement_std ON tr_stadedeveloppement_std.std_code::text = lot.lot_std_code::text 
     152   JOIN iav.tj_caracteristiquelot_car car ON car.car_lot_identifiant = lot.lot_identifiant 
     153   LEFT JOIN ref.tg_parametre_par par ON car.car_par_code::text = par.par_code::text 
     154   JOIN ref.tr_parametrequantitatif_qan qan ON qan.qan_par_code::text = par.par_code::text 
     155   LEFT JOIN iav.t_lot_lot lot_pere ON lot_pere.lot_identifiant = lot.lot_lot_identifiant 
     156   LEFT JOIN ref.tr_typequantitelot_qte qte_pere ON qte_pere.qte_code::text = lot_pere.lot_qte_code::text 
     157   LEFT JOIN ref.tr_devenirlot_dev dev_pere ON dev_pere.dev_code::text = lot_pere.lot_dev_code::text 
     158   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 
     159   FROM iav.tj_caracteristiquelot_car car_pere 
     160   LEFT JOIN ref.tg_parametre_par par_pere ON car_pere.car_par_code::text = par_pere.par_code::text 
     161   JOIN ref.tr_parametrequalitatif_qal qal_pere ON qal_pere.qal_par_code::text = par_pere.par_code::text 
     162   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; 
     163 
     164ALTER TABLE iav.vue_ope_lot_ech_parquan 
     165  OWNER TO postgres; 
     166GRANT ALL ON TABLE iav.vue_ope_lot_ech_parquan TO postgres; 
     167GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE iav.vue_ope_lot_ech_parquan TO iav; 
     168GRANT SELECT ON TABLE iav.vue_ope_lot_ech_parquan TO invite; 
     169 
     170 
     171 
     172 
     173 
     174 
    43175 
    44176}}}