main.dossiers vue

Colonnes

Définition

CREATE VIEW dossiers AS WITH d AS (SELECT * FROM dossiers.dossier_parlementaire QUALIFY (row_number() OVER (PARTITION BY uid ORDER BY _legislature DESC) = 1)), actes AS (SELECT _racine_id, min(date_acte) AS date_premier_acte, max(date_acte) AS date_dernier_acte, count_star() AS actes FROM dossiers.dossier_parlementaire_acte_legislatif GROUP BY ALL), docs AS (SELECT dossier_uid, count_star() AS documents FROM main.documents GROUP BY ALL)SELECT d.uid AS dossier_uid, d.legislature, d.titre_dossier_titre AS titre, d.titre_dossier_titre_chemin AS titre_chemin, d.procedure_parlementaire_libelle AS "procedure", d.indexation_themes_theme_libelle_theme AS theme, d.fusion_dossier_dossier_absorbant_ref AS dossier_absorbant_uid, a.date_premier_acte, a.date_dernier_acte, COALESCE(a.actes, 0) AS actes, COALESCE(docs.documents, 0) AS documents, ((('https://www.assemblee-nationale.fr/dyn/' || d.legislature) || '/dossiers/') || d.uid) AS url_an FROM d LEFT JOIN actes AS a ON ((a._racine_id = d._id)) LEFT JOIN docs ON ((docs.dossier_uid = d.uid));

Aperçu