SELECT

    --Coordonnées du service
    service.libelle as libelle_service, 
    service.adresse as adresse_service, 
    service.adresse2 as adresse2_service, 
    service.cp as cp_service, 
    service.ville as ville_service,
    service.delai as delai_service,
    CASE WHEN LOWER(service.delai_type) = LOWER('jour')
        THEN 'jour(s)'
        ELSE 'mois'
    END as delai_type_service,

    --Données générales du dossier d'instruction
    dossier.dossier_libelle as libelle_dossier,
    dossier.dossier as code_barres_dossier,
    replace(dossier.terrain_references_cadastrales, ';', ' ') as terrain_references_cadastrales_dossier,
    dossier_autorisation_type_detaille.libelle as libelle_datd, 

    --Adresse du terrain du dossier d'instruction
    dossier.terrain_adresse_voie_numero as terrain_adresse_voie_numero_dossier,
&DB_PREFIXEadresse(dossier.terrain_adresse_voie_numero::text, dossier.terrain_adresse_voie::text, ''::text, dossier.terrain_adresse_lieu_dit::text, dossier.terrain_adresse_bp::text, dossier.terrain_adresse_code_postal::text, dossier.terrain_adresse_localite::text, dossier.terrain_adresse_cedex::text) as adresse_terrain_sdl,
&DB_PREFIXEadresse(dossier.terrain_adresse_voie_numero::text, dossier.terrain_adresse_voie::text, ''::text, dossier.terrain_adresse_lieu_dit::text, dossier.terrain_adresse_bp::text, dossier.terrain_adresse_code_postal::text, dossier.terrain_adresse_localite::text, dossier.terrain_adresse_cedex::text, ''::text, ' '::text) as adresse_terrain,
    dossier.terrain_adresse_voie as terrain_adresse_voie_dossier,
    dossier.terrain_adresse_lieu_dit as terrain_adresse_lieu_dit_dossier,
    dossier.terrain_adresse_code_postal as terrain_adresse_code_postal_dossier,
    dossier.terrain_adresse_localite as terrain_adresse_localite_dossier,

    --Taxe d'aménagement du dossier d'instruction
    CASE
        WHEN tax_secteur = 1 THEN taxe_amenagement.tx_comm_secteur_1
        WHEN tax_secteur = 2 THEN taxe_amenagement.tx_comm_secteur_2
        WHEN tax_secteur = 3 THEN taxe_amenagement.tx_comm_secteur_3
        WHEN tax_secteur = 4 THEN taxe_amenagement.tx_comm_secteur_4
        WHEN tax_secteur = 5 THEN taxe_amenagement.tx_comm_secteur_5
        WHEN tax_secteur = 6 THEN taxe_amenagement.tx_comm_secteur_6
        WHEN tax_secteur = 7 THEN taxe_amenagement.tx_comm_secteur_7
        WHEN tax_secteur = 8 THEN taxe_amenagement.tx_comm_secteur_8
        WHEN tax_secteur = 9 THEN taxe_amenagement.tx_comm_secteur_9
        WHEN tax_secteur = 10 THEN taxe_amenagement.tx_comm_secteur_10
        WHEN tax_secteur = 11 THEN taxe_amenagement.tx_comm_secteur_11
        WHEN tax_secteur = 12 THEN taxe_amenagement.tx_comm_secteur_12
        WHEN tax_secteur = 13 THEN taxe_amenagement.tx_comm_secteur_13
        WHEN tax_secteur = 14 THEN taxe_amenagement.tx_comm_secteur_14
        WHEN tax_secteur = 15 THEN taxe_amenagement.tx_comm_secteur_15
        WHEN tax_secteur = 16 THEN taxe_amenagement.tx_comm_secteur_16
        WHEN tax_secteur = 17 THEN taxe_amenagement.tx_comm_secteur_17
        WHEN tax_secteur = 18 THEN taxe_amenagement.tx_comm_secteur_18
        WHEN tax_secteur = 19 THEN taxe_amenagement.tx_comm_secteur_19
        WHEN tax_secteur = 20 THEN taxe_amenagement.tx_comm_secteur_20
    END as tax_taux_secteur,

    taxe_amenagement.tx_comm_secteur_1 as tax_taux_secteur_1,
    taxe_amenagement.tx_comm_secteur_2 as tax_taux_secteur_2,
    taxe_amenagement.tx_comm_secteur_3 as tax_taux_secteur_3,
    taxe_amenagement.tx_comm_secteur_4 as tax_taux_secteur_4,
    taxe_amenagement.tx_comm_secteur_5 as tax_taux_secteur_5,
    taxe_amenagement.tx_comm_secteur_6 as tax_taux_secteur_6,
    taxe_amenagement.tx_comm_secteur_7 as tax_taux_secteur_7,
    taxe_amenagement.tx_comm_secteur_8 as tax_taux_secteur_8,
    taxe_amenagement.tx_comm_secteur_9 as tax_taux_secteur_9,
    taxe_amenagement.tx_comm_secteur_10 as tax_taux_secteur_10,
    taxe_amenagement.tx_comm_secteur_11 as tax_taux_secteur_11,
    taxe_amenagement.tx_comm_secteur_12 as tax_taux_secteur_12,
    taxe_amenagement.tx_comm_secteur_13 as tax_taux_secteur_13,
    taxe_amenagement.tx_comm_secteur_14 as tax_taux_secteur_14,
    taxe_amenagement.tx_comm_secteur_15 as tax_taux_secteur_15,
    taxe_amenagement.tx_comm_secteur_16 as tax_taux_secteur_16,
    taxe_amenagement.tx_comm_secteur_17 as tax_taux_secteur_17,
    taxe_amenagement.tx_comm_secteur_18 as tax_taux_secteur_18,
    taxe_amenagement.tx_comm_secteur_19 as tax_taux_secteur_19,
    taxe_amenagement.tx_comm_secteur_20 as tax_taux_secteur_20,

    CASE WHEN taxe_amenagement.en_ile_de_france IS NULL
        OR taxe_amenagement.en_ile_de_france IS FALSE
        THEN 'Non'
        ELSE 'Oui'
    END as tax_en_ile_de_france,
    taxe_amenagement.val_forf_surf_cstr as tax_val_forf_surf_cstr,
    taxe_amenagement.val_forf_empl_tente_carav_rml as tax_val_forf_empl_tente_carav_rml,
    taxe_amenagement.val_forf_empl_hll as tax_val_forf_empl_hll,
    taxe_amenagement.val_forf_surf_piscine as tax_val_forf_surf_piscine,
    taxe_amenagement.val_forf_nb_eolienne as tax_val_forf_nb_eolienne,
    taxe_amenagement.val_forf_surf_pann_photo as tax_val_forf_surf_pann_photo,
    taxe_amenagement.val_forf_nb_parking_ext as tax_val_forf_nb_parking_ext,
    taxe_amenagement.tx_depart  as tax_tx_depart,
    taxe_amenagement.tx_reg as tax_tx_reg,
    taxe_amenagement.tx_exo_facul_1_reg as tax_tx_exo_facul_1_reg,
    taxe_amenagement.tx_exo_facul_2_reg as tax_tx_exo_facul_2_reg,
    taxe_amenagement.tx_exo_facul_3_reg as tax_tx_exo_facul_3_reg,
    taxe_amenagement.tx_exo_facul_4_reg as tax_tx_exo_facul_4_reg,
    taxe_amenagement.tx_exo_facul_5_reg as tax_tx_exo_facul_5_reg,
    taxe_amenagement.tx_exo_facul_6_reg as tax_tx_exo_facul_6_reg,
    taxe_amenagement.tx_exo_facul_7_reg as tax_tx_exo_facul_7_reg,
    taxe_amenagement.tx_exo_facul_8_reg as tax_tx_exo_facul_8_reg,
    taxe_amenagement.tx_exo_facul_9_reg as tax_tx_exo_facul_9_reg,
    taxe_amenagement.tx_exo_facul_1_depart  as tax_tx_exo_facul_1_depart,
    taxe_amenagement.tx_exo_facul_2_depart  as tax_tx_exo_facul_2_depart,
    taxe_amenagement.tx_exo_facul_3_depart  as tax_tx_exo_facul_3_depart,
    taxe_amenagement.tx_exo_facul_4_depart  as tax_tx_exo_facul_4_depart,
    taxe_amenagement.tx_exo_facul_5_depart  as tax_tx_exo_facul_5_depart,
    taxe_amenagement.tx_exo_facul_6_depart  as tax_tx_exo_facul_6_depart,
    taxe_amenagement.tx_exo_facul_7_depart  as tax_tx_exo_facul_7_depart,
    taxe_amenagement.tx_exo_facul_8_depart  as tax_tx_exo_facul_8_depart,
    taxe_amenagement.tx_exo_facul_9_depart  as tax_tx_exo_facul_9_depart,
    taxe_amenagement.tx_exo_facul_1_comm as tax_tx_exo_facul_1_comm,
    taxe_amenagement.tx_exo_facul_2_comm as tax_tx_exo_facul_2_comm,
    taxe_amenagement.tx_exo_facul_3_comm as tax_tx_exo_facul_3_comm,
    taxe_amenagement.tx_exo_facul_4_comm as tax_tx_exo_facul_4_comm,
    taxe_amenagement.tx_exo_facul_5_comm as tax_tx_exo_facul_5_comm,
    taxe_amenagement.tx_exo_facul_6_comm as tax_tx_exo_facul_6_comm,
    taxe_amenagement.tx_exo_facul_7_comm as tax_tx_exo_facul_7_comm,
    taxe_amenagement.tx_exo_facul_8_comm as tax_tx_exo_facul_8_comm,
    taxe_amenagement.tx_exo_facul_9_comm as tax_tx_exo_facul_9_comm,
    taxe_amenagement.tx_rap as tax_tx_rap,

    dossier.tax_mtn_rap as tax_montant_rap,
    CONCAT_WS(' ', 'Secteur', dossier.tax_secteur) as tax_secteur,
    dossier.tax_secteur as tax_numero_secteur,
    dossier.tax_mtn_part_commu as tax_montant_part_communale,
    dossier.tax_mtn_part_depart as tax_montant_part_departementale,
    dossier.tax_mtn_part_reg as tax_montant_part_regionale,
    dossier.tax_mtn_total as tax_montant_total,

    --Coordonnées de l'instructeur
    instructeur.nom as nom_instructeur,
    instructeur.telephone as tel_instructeur,
    instructeur.telephone as telephone_instructeur,
    instructeur_utilisateur.email as email_instructeur,

    --Coordonnées du demandeur
    civilite.libelle as civilite_demandeur,
    CASE WHEN demandeur.qualite='particulier'
        THEN TRIM(CONCAT(demandeur.particulier_nom, ' ', demandeur.particulier_prenom))
        ELSE TRIM(CONCAT(demandeur.personne_morale_raison_sociale, ' ', demandeur.personne_morale_denomination))
    END as nom_demandeur,
    CONCAT(demandeur.numero, ' ', demandeur.voie) as adresse_demandeur,
    demandeur.complement as complement_adresse_demandeur,
    demandeur.lieu_dit as lieu_dit_demandeur,
    demandeur.code_postal as code_postal_demandeur,
    demandeur.localite as ville_demandeur,
    demandeur.personne_morale_denomination as societe_demandeur,

    -- Coordonnées du pétitionnaire principal
    CASE WHEN petitionnaire_principal.qualite='particulier'
        THEN TRIM(CONCAT_WS(' ', petitionnaire_principal_civilite.libelle, petitionnaire_principal.particulier_nom, petitionnaire_principal.particulier_prenom))
        ELSE
            CASE WHEN petitionnaire_principal.personne_morale_nom IS NOT NULL OR petitionnaire_principal.personne_morale_prenom IS NOT NULL
                THEN TRIM(CONCAT_WS(' ', petitionnaire_principal.personne_morale_raison_sociale, petitionnaire_principal.personne_morale_denomination, 'représenté(e) par', petitionnaire_principal_civilite.libelle, petitionnaire_principal.personne_morale_nom, petitionnaire_principal.personne_morale_prenom))
                ELSE TRIM(CONCAT(petitionnaire_principal.personne_morale_raison_sociale, ' ', petitionnaire_principal.personne_morale_denomination))
            END
    END as nom_complet_qualite_part_ou_pm_petitionnaire_principal,
    CASE WHEN petitionnaire_principal.qualite='particulier' OR petitionnaire_principal.personne_morale_nom IS NOT NULL OR petitionnaire_principal.personne_morale_prenom IS NOT NULL
        THEN petitionnaire_principal_civilite.libelle
        ELSE ''
    END as civilite_qualite_part_ou_pm_petitionnaire_principal,
    CASE WHEN petitionnaire_principal.qualite='particulier'
        THEN petitionnaire_principal.particulier_nom
        ELSE
            CASE WHEN petitionnaire_principal.personne_morale_nom IS NOT NULL
                THEN petitionnaire_principal.personne_morale_nom
                ELSE ''
            END
    END as nom_qualite_part_ou_pm_petitionnaire_principal,
    CASE WHEN petitionnaire_principal.qualite='particulier'
        THEN petitionnaire_principal.particulier_prenom
        ELSE
            CASE WHEN petitionnaire_principal.personne_morale_prenom IS NOT NULL
                THEN petitionnaire_principal.personne_morale_prenom
                ELSE ''
            END
    END as prenom_qualite_part_ou_pm_petitionnaire_principal,
    CASE WHEN petitionnaire_principal.qualite='particulier'
        THEN TRIM(CONCAT_WS(' ', petitionnaire_principal_civilite.libelle, petitionnaire_principal.particulier_nom, petitionnaire_principal.particulier_prenom))
        ELSE ''
    END as nom_complet_qualite_part_petitionnaire_principal,
    CASE WHEN petitionnaire_principal.qualite='particulier'
        THEN petitionnaire_principal_civilite.libelle
        ELSE ''
    END as civilite_qualite_part_petitionnaire_principal,
    CASE WHEN petitionnaire_principal.qualite='particulier'
        THEN petitionnaire_principal.particulier_nom
        ELSE ''
    END as nom_qualite_part_petitionnaire_principal,
    CASE WHEN petitionnaire_principal.qualite='particulier'
        THEN petitionnaire_principal.particulier_prenom
        ELSE ''
    END as prenom_qualite_part_petitionnaire_principal,
    CASE WHEN petitionnaire_principal.personne_morale_nom IS NOT NULL OR petitionnaire_principal.personne_morale_prenom IS NOT NULL
        THEN TRIM(CONCAT_WS(' ', petitionnaire_principal.personne_morale_raison_sociale, petitionnaire_principal.personne_morale_denomination, 'représenté(e) par', petitionnaire_principal_civilite.libelle, petitionnaire_principal.personne_morale_nom, petitionnaire_principal.personne_morale_prenom))
        ELSE TRIM(CONCAT(petitionnaire_principal.personne_morale_raison_sociale, ' ', petitionnaire_principal.personne_morale_denomination))
    END as nom_complet_qualite_pm_petitionnaire_principal,
    CASE WHEN petitionnaire_principal.personne_morale_nom IS NOT NULL OR petitionnaire_principal.personne_morale_prenom IS NOT NULL
        THEN petitionnaire_principal_civilite.libelle
        ELSE ''
    END as civilite_qualite_pm_petitionnaire_principal,
    CASE WHEN petitionnaire_principal.personne_morale_nom IS NOT NULL
        THEN petitionnaire_principal.personne_morale_nom
        ELSE ''
    END as nom_qualite_pm_petitionnaire_principal,
    CASE WHEN petitionnaire_principal.personne_morale_prenom IS NOT NULL
        THEN petitionnaire_principal.personne_morale_prenom
        ELSE ''
    END as prenom_qualite_pm_petitionnaire_principal,
    CASE WHEN petitionnaire_principal.qualite='particulier'
        THEN TRIM(CONCAT_WS(' ', petitionnaire_principal_civilite.libelle, petitionnaire_principal.particulier_nom, petitionnaire_principal.particulier_prenom))
        ELSE
            CASE WHEN petitionnaire_principal.personne_morale_nom IS NOT NULL OR petitionnaire_principal.personne_morale_prenom IS NOT NULL
                THEN TRIM(CONCAT_WS(' ', petitionnaire_principal.personne_morale_raison_sociale, petitionnaire_principal.personne_morale_denomination, 'représenté(e) par', petitionnaire_principal_civilite.libelle, petitionnaire_principal.personne_morale_nom, petitionnaire_principal.personne_morale_prenom))
                ELSE TRIM(CONCAT(petitionnaire_principal.personne_morale_raison_sociale, ' ', petitionnaire_principal.personne_morale_denomination))
            END
    END as nom_petitionnaire_principal,
    CASE WHEN petitionnaire_principal.qualite='particulier' OR petitionnaire_principal.personne_morale_nom IS NOT NULL OR petitionnaire_principal.personne_morale_prenom IS NOT NULL
        THEN petitionnaire_principal_civilite.libelle
        ELSE ''
    END as civilite_petitionnaire_principal,
    CASE WHEN petitionnaire_principal.qualite='particulier'
        THEN petitionnaire_principal.particulier_nom
        ELSE
            CASE WHEN petitionnaire_principal.personne_morale_nom IS NOT NULL
                THEN petitionnaire_principal.personne_morale_nom
                ELSE ''
            END
    END as nom_particulier_petitionnaire_principal,
    CASE WHEN petitionnaire_principal.qualite='particulier'
        THEN petitionnaire_principal.particulier_prenom
        ELSE
            CASE WHEN petitionnaire_principal.personne_morale_prenom IS NOT NULL
                THEN petitionnaire_principal.personne_morale_prenom
                ELSE ''
            END
    END as prenom_particulier_petitionnaire_principal,
    CASE WHEN petitionnaire_principal.qualite='particulier'
        THEN ''
        ELSE petitionnaire_principal.personne_morale_raison_sociale
    END as raison_sociale_petitionnaire_principal,
    CASE WHEN petitionnaire_principal.qualite='particulier'
        THEN ''
        ELSE petitionnaire_principal.personne_morale_denomination
    END as denomination_petitionnaire_principal,
    petitionnaire_principal.numero as numero_petitionnaire_principal,
&DB_PREFIXEadresse(petitionnaire_principal.numero::text, petitionnaire_principal.voie::text, petitionnaire_principal.complement::text, petitionnaire_principal.lieu_dit::text, petitionnaire_principal.bp::text, petitionnaire_principal.code_postal::text, petitionnaire_principal.localite::text, petitionnaire_principal.cedex::text, petitionnaire_principal.pays::text) as adresse_petitionnaire_principal_sdl,
&DB_PREFIXEadresse(petitionnaire_principal.numero::text, petitionnaire_principal.voie::text, petitionnaire_principal.complement::text, petitionnaire_principal.lieu_dit::text, petitionnaire_principal.bp::text, petitionnaire_principal.code_postal::text, petitionnaire_principal.localite::text, petitionnaire_principal.cedex::text, petitionnaire_principal.pays::text, ' '::text) as adresse_petitionnaire_principal,
    petitionnaire_principal.voie as voie_petitionnaire_principal,
    petitionnaire_principal.complement as complement_petitionnaire_principal,
    petitionnaire_principal.lieu_dit as lieu_dit_petitionnaire_principal,
    CASE 
        WHEN petitionnaire_principal.bp IS NULL
        THEN ''
        ELSE CONCAT('BP ', petitionnaire_principal.bp)
    END as bp_petitionnaire_principal,
    petitionnaire_principal.code_postal as code_postal_petitionnaire_principal,
    petitionnaire_principal.localite as localite_petitionnaire_principal,
    CASE 
        WHEN petitionnaire_principal.cedex IS NULL
        THEN ''
        ELSE CONCAT('CEDEX ', petitionnaire_principal.cedex)
    END as cedex_petitionnaire_principal,
    petitionnaire_principal.pays as pays_petitionnaire_principal,

    --Dates importantes du dossier d'instruction
to_char(dossier.date_depot_mairie, 'DD/MM/YYYY') as date_depot_mairie,
to_char(dossier.date_affichage, 'DD/MM/YYYY') as date_affichage,
    to_char(dossier.date_depot,'DD/MM/YYYY') as date_depot_dossier,
    to_char(dossier.date_complet, 'DD/MM/YYYY') as date_completude,
    to_char(dossier.date_dernier_depot, 'DD/MM/YYYY') as date_dernier_depot,
    to_char(dossier.date_rejet,'DD/MM/YYYY') as date_rejet_dossier,
    CASE WHEN dossier.incomplet_notifie IS TRUE AND dossier.incompletude IS TRUE
        THEN to_char(dossier.date_limite_incompletude ,'DD/MM/YYYY')
        ELSE to_char(dossier.date_limite ,'DD/MM/YYYY')
    END as date_limite_dossier,
    to_char(consultation.date_envoi,'DD/MM/YYYY') as date_envoi_dossier,
    to_char(instruction.date_evenement,'DD/MM/YYYY') as date_evenement,
    dossier.delai as delai_limite_decision,

    --Code barres de la consultation
    consultation.code_barres as code_barres_consultation,

    --Commentaire de consultation
    consultation.commentaire as commentaire_consultation,

    --Données générales des données techniques
    donnees_techniques.co_projet_desc as co_projet_desc_donnees_techniques,
    donnees_techniques.am_projet_desc as am_projet_desc_donnees_techniques,
    donnees_techniques.dm_projet_desc as dm_projet_desc_donnees_techniques,
    donnees_techniques.ope_proj_desc as ope_proj_desc_donnees_techniques,
    TRIM(CONCAT(
        donnees_techniques.co_projet_desc, ' ',
        donnees_techniques.am_projet_desc, ' ',
        donnees_techniques.dm_projet_desc, ' ',
        donnees_techniques.ope_proj_desc
        )) as projet_desc_donnees_techniques,
    donnees_techniques.am_lot_max_nb as am_lot_max_nb_donnees_techniques,
    donnees_techniques.am_lot_max_shon as am_lot_max_shon_donnees_techniques,
    -- Si une valeur est saisie dans la deuxième version du tableau des surfaces
    -- alors on récupère seulement ses valeurs
    CASE WHEN su2_avt_shon1 IS NOT NULL
        OR su2_avt_shon2 IS NOT NULL
        OR su2_avt_shon3 IS NOT NULL
        OR su2_avt_shon4 IS NOT NULL
        OR su2_avt_shon5 IS NOT NULL
        OR su2_avt_shon6 IS NOT NULL
        OR su2_avt_shon7 IS NOT NULL
        OR su2_avt_shon8 IS NOT NULL
        OR su2_avt_shon9 IS NOT NULL
        OR su2_avt_shon10 IS NOT NULL
        OR su2_avt_shon11 IS NOT NULL
        OR su2_avt_shon12 IS NOT NULL
        OR su2_avt_shon13 IS NOT NULL
        OR su2_avt_shon14 IS NOT NULL
        OR su2_avt_shon15 IS NOT NULL
        OR su2_avt_shon16 IS NOT NULL
        OR su2_avt_shon17 IS NOT NULL
        OR su2_avt_shon18 IS NOT NULL
        OR su2_avt_shon19 IS NOT NULL
        OR su2_avt_shon20 IS NOT NULL
        OR su2_avt_shon21 IS NOT NULL
        OR su2_avt_shon22 IS NOT NULL
        OR su2_avt_shon23 IS NOT NULL
        OR su2_avt_shon24 IS NOT NULL
        OR su2_cstr_shon1 IS NOT NULL
        OR su2_cstr_shon2 IS NOT NULL
        OR su2_cstr_shon3 IS NOT NULL
        OR su2_cstr_shon4 IS NOT NULL
        OR su2_cstr_shon5 IS NOT NULL
        OR su2_cstr_shon6 IS NOT NULL
        OR su2_cstr_shon7 IS NOT NULL
        OR su2_cstr_shon8 IS NOT NULL
        OR su2_cstr_shon9 IS NOT NULL
        OR su2_cstr_shon10 IS NOT NULL
        OR su2_cstr_shon11 IS NOT NULL
        OR su2_cstr_shon12 IS NOT NULL
        OR su2_cstr_shon13 IS NOT NULL
        OR su2_cstr_shon14 IS NOT NULL
        OR su2_cstr_shon15 IS NOT NULL
        OR su2_cstr_shon16 IS NOT NULL
        OR su2_cstr_shon17 IS NOT NULL
        OR su2_cstr_shon18 IS NOT NULL
        OR su2_cstr_shon19 IS NOT NULL
        OR su2_cstr_shon20 IS NOT NULL
        OR su2_cstr_shon21 IS NOT NULL
        OR su2_cstr_shon22 IS NOT NULL
        OR su2_cstr_shon23 IS NOT NULL
        OR su2_cstr_shon24 IS NOT NULL
        OR su2_chge_shon1 IS NOT NULL
        OR su2_chge_shon2 IS NOT NULL
        OR su2_chge_shon3 IS NOT NULL
        OR su2_chge_shon4 IS NOT NULL
        OR su2_chge_shon5 IS NOT NULL
        OR su2_chge_shon6 IS NOT NULL
        OR su2_chge_shon7 IS NOT NULL
        OR su2_chge_shon8 IS NOT NULL
        OR su2_chge_shon9 IS NOT NULL
        OR su2_chge_shon10 IS NOT NULL
        OR su2_chge_shon11 IS NOT NULL
        OR su2_chge_shon12 IS NOT NULL
        OR su2_chge_shon13 IS NOT NULL
        OR su2_chge_shon14 IS NOT NULL
        OR su2_chge_shon15 IS NOT NULL
        OR su2_chge_shon16 IS NOT NULL
        OR su2_chge_shon17 IS NOT NULL
        OR su2_chge_shon18 IS NOT NULL
        OR su2_chge_shon19 IS NOT NULL
        OR su2_chge_shon20 IS NOT NULL
        OR su2_chge_shon21 IS NOT NULL
        OR su2_chge_shon22 IS NOT NULL
        OR su2_chge_shon23 IS NOT NULL
        OR su2_chge_shon24 IS NOT NULL
        OR su2_demo_shon1 IS NOT NULL
        OR su2_demo_shon2 IS NOT NULL
        OR su2_demo_shon3 IS NOT NULL
        OR su2_demo_shon4 IS NOT NULL
        OR su2_demo_shon5 IS NOT NULL
        OR su2_demo_shon6 IS NOT NULL
        OR su2_demo_shon7 IS NOT NULL
        OR su2_demo_shon8 IS NOT NULL
        OR su2_demo_shon9 IS NOT NULL
        OR su2_demo_shon10 IS NOT NULL
        OR su2_demo_shon11 IS NOT NULL
        OR su2_demo_shon12 IS NOT NULL
        OR su2_demo_shon13 IS NOT NULL
        OR su2_demo_shon14 IS NOT NULL
        OR su2_demo_shon15 IS NOT NULL
        OR su2_demo_shon16 IS NOT NULL
        OR su2_demo_shon17 IS NOT NULL
        OR su2_demo_shon18 IS NOT NULL
        OR su2_demo_shon19 IS NOT NULL
        OR su2_demo_shon20 IS NOT NULL
        OR su2_demo_shon21 IS NOT NULL
        OR su2_demo_shon22 IS NOT NULL
        OR su2_demo_shon23 IS NOT NULL
        OR su2_demo_shon24 IS NOT NULL
        OR su2_sup_shon1 IS NOT NULL
        OR su2_sup_shon2 IS NOT NULL
        OR su2_sup_shon3 IS NOT NULL
        OR su2_sup_shon4 IS NOT NULL
        OR su2_sup_shon5 IS NOT NULL
        OR su2_sup_shon6 IS NOT NULL
        OR su2_sup_shon7 IS NOT NULL
        OR su2_sup_shon8 IS NOT NULL
        OR su2_sup_shon9 IS NOT NULL
        OR su2_sup_shon10 IS NOT NULL
        OR su2_sup_shon11 IS NOT NULL
        OR su2_sup_shon12 IS NOT NULL
        OR su2_sup_shon13 IS NOT NULL
        OR su2_sup_shon14 IS NOT NULL
        OR su2_sup_shon15 IS NOT NULL
        OR su2_sup_shon16 IS NOT NULL
        OR su2_sup_shon17 IS NOT NULL
        OR su2_sup_shon18 IS NOT NULL
        OR su2_sup_shon19 IS NOT NULL
        OR su2_sup_shon20 IS NOT NULL
        OR su2_sup_shon21 IS NOT NULL
        OR su2_sup_shon22 IS NOT NULL
        OR su2_sup_shon23 IS NOT NULL
        OR su2_sup_shon24 IS NOT NULL
        THEN donnees_techniques.su2_cstr_shon_tot
        ELSE donnees_techniques.su_cstr_shon_tot
    END as su_cstr_shon_tot_donnees_techniques,
    -- Si une valeur est saisie dans la deuxième version du tableau des surfaces
    -- alors on récupère seulement ses valeurs
    CASE WHEN su2_avt_shon1 IS NOT NULL
        OR su2_avt_shon2 IS NOT NULL
        OR su2_avt_shon3 IS NOT NULL
        OR su2_avt_shon4 IS NOT NULL
        OR su2_avt_shon5 IS NOT NULL
        OR su2_avt_shon6 IS NOT NULL
        OR su2_avt_shon7 IS NOT NULL
        OR su2_avt_shon8 IS NOT NULL
        OR su2_avt_shon9 IS NOT NULL
        OR su2_avt_shon10 IS NOT NULL
        OR su2_avt_shon11 IS NOT NULL
        OR su2_avt_shon12 IS NOT NULL
        OR su2_avt_shon13 IS NOT NULL
        OR su2_avt_shon14 IS NOT NULL
        OR su2_avt_shon15 IS NOT NULL
        OR su2_avt_shon16 IS NOT NULL
        OR su2_avt_shon17 IS NOT NULL
        OR su2_avt_shon18 IS NOT NULL
        OR su2_avt_shon19 IS NOT NULL
        OR su2_avt_shon20 IS NOT NULL
        OR su2_avt_shon21 IS NOT NULL
        OR su2_avt_shon22 IS NOT NULL
        OR su2_avt_shon23 IS NOT NULL
        OR su2_avt_shon24 IS NOT NULL
        OR su2_cstr_shon1 IS NOT NULL
        OR su2_cstr_shon2 IS NOT NULL
        OR su2_cstr_shon3 IS NOT NULL
        OR su2_cstr_shon4 IS NOT NULL
        OR su2_cstr_shon5 IS NOT NULL
        OR su2_cstr_shon6 IS NOT NULL
        OR su2_cstr_shon7 IS NOT NULL
        OR su2_cstr_shon8 IS NOT NULL
        OR su2_cstr_shon9 IS NOT NULL
        OR su2_cstr_shon10 IS NOT NULL
        OR su2_cstr_shon11 IS NOT NULL
        OR su2_cstr_shon12 IS NOT NULL
        OR su2_cstr_shon13 IS NOT NULL
        OR su2_cstr_shon14 IS NOT NULL
        OR su2_cstr_shon15 IS NOT NULL
        OR su2_cstr_shon16 IS NOT NULL
        OR su2_cstr_shon17 IS NOT NULL
        OR su2_cstr_shon18 IS NOT NULL
        OR su2_cstr_shon19 IS NOT NULL
        OR su2_cstr_shon20 IS NOT NULL
        OR su2_cstr_shon21 IS NOT NULL
        OR su2_cstr_shon22 IS NOT NULL
        OR su2_cstr_shon23 IS NOT NULL
        OR su2_cstr_shon24 IS NOT NULL
        OR su2_chge_shon1 IS NOT NULL
        OR su2_chge_shon2 IS NOT NULL
        OR su2_chge_shon3 IS NOT NULL
        OR su2_chge_shon4 IS NOT NULL
        OR su2_chge_shon5 IS NOT NULL
        OR su2_chge_shon6 IS NOT NULL
        OR su2_chge_shon7 IS NOT NULL
        OR su2_chge_shon8 IS NOT NULL
        OR su2_chge_shon9 IS NOT NULL
        OR su2_chge_shon10 IS NOT NULL
        OR su2_chge_shon11 IS NOT NULL
        OR su2_chge_shon12 IS NOT NULL
        OR su2_chge_shon13 IS NOT NULL
        OR su2_chge_shon14 IS NOT NULL
        OR su2_chge_shon15 IS NOT NULL
        OR su2_chge_shon16 IS NOT NULL
        OR su2_chge_shon17 IS NOT NULL
        OR su2_chge_shon18 IS NOT NULL
        OR su2_chge_shon19 IS NOT NULL
        OR su2_chge_shon20 IS NOT NULL
        OR su2_chge_shon21 IS NOT NULL
        OR su2_chge_shon22 IS NOT NULL
        OR su2_chge_shon23 IS NOT NULL
        OR su2_chge_shon24 IS NOT NULL
        OR su2_demo_shon1 IS NOT NULL
        OR su2_demo_shon2 IS NOT NULL
        OR su2_demo_shon3 IS NOT NULL
        OR su2_demo_shon4 IS NOT NULL
        OR su2_demo_shon5 IS NOT NULL
        OR su2_demo_shon6 IS NOT NULL
        OR su2_demo_shon7 IS NOT NULL
        OR su2_demo_shon8 IS NOT NULL
        OR su2_demo_shon9 IS NOT NULL
        OR su2_demo_shon10 IS NOT NULL
        OR su2_demo_shon11 IS NOT NULL
        OR su2_demo_shon12 IS NOT NULL
        OR su2_demo_shon13 IS NOT NULL
        OR su2_demo_shon14 IS NOT NULL
        OR su2_demo_shon15 IS NOT NULL
        OR su2_demo_shon16 IS NOT NULL
        OR su2_demo_shon17 IS NOT NULL
        OR su2_demo_shon18 IS NOT NULL
        OR su2_demo_shon19 IS NOT NULL
        OR su2_demo_shon20 IS NOT NULL
        OR su2_demo_shon21 IS NOT NULL
        OR su2_demo_shon22 IS NOT NULL
        OR su2_demo_shon23 IS NOT NULL
        OR su2_demo_shon24 IS NOT NULL
        OR su2_sup_shon1 IS NOT NULL
        OR su2_sup_shon2 IS NOT NULL
        OR su2_sup_shon3 IS NOT NULL
        OR su2_sup_shon4 IS NOT NULL
        OR su2_sup_shon5 IS NOT NULL
        OR su2_sup_shon6 IS NOT NULL
        OR su2_sup_shon7 IS NOT NULL
        OR su2_sup_shon8 IS NOT NULL
        OR su2_sup_shon9 IS NOT NULL
        OR su2_sup_shon10 IS NOT NULL
        OR su2_sup_shon11 IS NOT NULL
        OR su2_sup_shon12 IS NOT NULL
        OR su2_sup_shon13 IS NOT NULL
        OR su2_sup_shon14 IS NOT NULL
        OR su2_sup_shon15 IS NOT NULL
        OR su2_sup_shon16 IS NOT NULL
        OR su2_sup_shon17 IS NOT NULL
        OR su2_sup_shon18 IS NOT NULL
        OR su2_sup_shon19 IS NOT NULL
        OR su2_sup_shon20 IS NOT NULL
        OR su2_sup_shon21 IS NOT NULL
        OR su2_sup_shon22 IS NOT NULL
        OR su2_sup_shon23 IS NOT NULL
        OR su2_sup_shon24 IS NOT NULL
        THEN donnees_techniques.su2_demo_shon_tot
        ELSE donnees_techniques.su_demo_shon_tot
    END as su_demo_shon_tot_donnees_techniques,
    -- Si une valeur est saisie dans la deuxième version du tableau des surfaces
    -- alors on récupère seulement ses valeurs
    CASE WHEN su2_avt_shon1 IS NOT NULL
        OR su2_avt_shon2 IS NOT NULL
        OR su2_avt_shon3 IS NOT NULL
        OR su2_avt_shon4 IS NOT NULL
        OR su2_avt_shon5 IS NOT NULL
        OR su2_avt_shon6 IS NOT NULL
        OR su2_avt_shon7 IS NOT NULL
        OR su2_avt_shon8 IS NOT NULL
        OR su2_avt_shon9 IS NOT NULL
        OR su2_avt_shon10 IS NOT NULL
        OR su2_avt_shon11 IS NOT NULL
        OR su2_avt_shon12 IS NOT NULL
        OR su2_avt_shon13 IS NOT NULL
        OR su2_avt_shon14 IS NOT NULL
        OR su2_avt_shon15 IS NOT NULL
        OR su2_avt_shon16 IS NOT NULL
        OR su2_avt_shon17 IS NOT NULL
        OR su2_avt_shon18 IS NOT NULL
        OR su2_avt_shon19 IS NOT NULL
        OR su2_avt_shon20 IS NOT NULL
        OR su2_avt_shon21 IS NOT NULL
        OR su2_avt_shon22 IS NOT NULL
        OR su2_avt_shon23 IS NOT NULL
        OR su2_avt_shon24 IS NOT NULL
        OR su2_cstr_shon1 IS NOT NULL
        OR su2_cstr_shon2 IS NOT NULL
        OR su2_cstr_shon3 IS NOT NULL
        OR su2_cstr_shon4 IS NOT NULL
        OR su2_cstr_shon5 IS NOT NULL
        OR su2_cstr_shon6 IS NOT NULL
        OR su2_cstr_shon7 IS NOT NULL
        OR su2_cstr_shon8 IS NOT NULL
        OR su2_cstr_shon9 IS NOT NULL
        OR su2_cstr_shon10 IS NOT NULL
        OR su2_cstr_shon11 IS NOT NULL
        OR su2_cstr_shon12 IS NOT NULL
        OR su2_cstr_shon13 IS NOT NULL
        OR su2_cstr_shon14 IS NOT NULL
        OR su2_cstr_shon15 IS NOT NULL
        OR su2_cstr_shon16 IS NOT NULL
        OR su2_cstr_shon17 IS NOT NULL
        OR su2_cstr_shon18 IS NOT NULL
        OR su2_cstr_shon19 IS NOT NULL
        OR su2_cstr_shon20 IS NOT NULL
        OR su2_cstr_shon21 IS NOT NULL
        OR su2_cstr_shon22 IS NOT NULL
        OR su2_cstr_shon23 IS NOT NULL
        OR su2_cstr_shon24 IS NOT NULL
        OR su2_chge_shon1 IS NOT NULL
        OR su2_chge_shon2 IS NOT NULL
        OR su2_chge_shon3 IS NOT NULL
        OR su2_chge_shon4 IS NOT NULL
        OR su2_chge_shon5 IS NOT NULL
        OR su2_chge_shon6 IS NOT NULL
        OR su2_chge_shon7 IS NOT NULL
        OR su2_chge_shon8 IS NOT NULL
        OR su2_chge_shon9 IS NOT NULL
        OR su2_chge_shon10 IS NOT NULL
        OR su2_chge_shon11 IS NOT NULL
        OR su2_chge_shon12 IS NOT NULL
        OR su2_chge_shon13 IS NOT NULL
        OR su2_chge_shon14 IS NOT NULL
        OR su2_chge_shon15 IS NOT NULL
        OR su2_chge_shon16 IS NOT NULL
        OR su2_chge_shon17 IS NOT NULL
        OR su2_chge_shon18 IS NOT NULL
        OR su2_chge_shon19 IS NOT NULL
        OR su2_chge_shon20 IS NOT NULL
        OR su2_chge_shon21 IS NOT NULL
        OR su2_chge_shon22 IS NOT NULL
        OR su2_chge_shon23 IS NOT NULL
        OR su2_chge_shon24 IS NOT NULL
        OR su2_demo_shon1 IS NOT NULL
        OR su2_demo_shon2 IS NOT NULL
        OR su2_demo_shon3 IS NOT NULL
        OR su2_demo_shon4 IS NOT NULL
        OR su2_demo_shon5 IS NOT NULL
        OR su2_demo_shon6 IS NOT NULL
        OR su2_demo_shon7 IS NOT NULL
        OR su2_demo_shon8 IS NOT NULL
        OR su2_demo_shon9 IS NOT NULL
        OR su2_demo_shon10 IS NOT NULL
        OR su2_demo_shon11 IS NOT NULL
        OR su2_demo_shon12 IS NOT NULL
        OR su2_demo_shon13 IS NOT NULL
        OR su2_demo_shon14 IS NOT NULL
        OR su2_demo_shon15 IS NOT NULL
        OR su2_demo_shon16 IS NOT NULL
        OR su2_demo_shon17 IS NOT NULL
        OR su2_demo_shon18 IS NOT NULL
        OR su2_demo_shon19 IS NOT NULL
        OR su2_demo_shon20 IS NOT NULL
        OR su2_demo_shon21 IS NOT NULL
        OR su2_demo_shon22 IS NOT NULL
        OR su2_demo_shon23 IS NOT NULL
        OR su2_demo_shon24 IS NOT NULL
        OR su2_sup_shon1 IS NOT NULL
        OR su2_sup_shon2 IS NOT NULL
        OR su2_sup_shon3 IS NOT NULL
        OR su2_sup_shon4 IS NOT NULL
        OR su2_sup_shon5 IS NOT NULL
        OR su2_sup_shon6 IS NOT NULL
        OR su2_sup_shon7 IS NOT NULL
        OR su2_sup_shon8 IS NOT NULL
        OR su2_sup_shon9 IS NOT NULL
        OR su2_sup_shon10 IS NOT NULL
        OR su2_sup_shon11 IS NOT NULL
        OR su2_sup_shon12 IS NOT NULL
        OR su2_sup_shon13 IS NOT NULL
        OR su2_sup_shon14 IS NOT NULL
        OR su2_sup_shon15 IS NOT NULL
        OR su2_sup_shon16 IS NOT NULL
        OR su2_sup_shon17 IS NOT NULL
        OR su2_sup_shon18 IS NOT NULL
        OR su2_sup_shon19 IS NOT NULL
        OR su2_sup_shon20 IS NOT NULL
        OR su2_sup_shon21 IS NOT NULL
        OR su2_sup_shon22 IS NOT NULL
        OR su2_sup_shon23 IS NOT NULL
        OR su2_sup_shon24 IS NOT NULL
        THEN
            REGEXP_REPLACE(CONCAT(
                CASE WHEN donnees_techniques.su2_cstr_shon1 IS NULL
                    THEN ''
                    ELSE CONCAT ('Exploitation agricole - ', donnees_techniques.su2_cstr_shon1, ' m² / ')
                END,
                CASE WHEN donnees_techniques.su2_cstr_shon2 IS NULL
                    THEN ''
                    ELSE CONCAT ('Exploitation forestière - ', donnees_techniques.su2_cstr_shon2, ' m² / ')
                END,
                CASE WHEN donnees_techniques.su2_cstr_shon3 IS NULL
                    THEN ''
                    ELSE CONCAT ('Logement - ', donnees_techniques.su2_cstr_shon3, ' m² / ')
                END,
                CASE WHEN donnees_techniques.su2_cstr_shon4 IS NULL
                    THEN ''
                    ELSE CONCAT ('Hébergement - ', donnees_techniques.su2_cstr_shon4, ' m² / ')
                END,
                CASE WHEN donnees_techniques.su2_cstr_shon5 IS NULL
                    THEN ''
                    ELSE CONCAT ('Artisanat et commerce de détail - ', donnees_techniques.su2_cstr_shon5, ' m² / ')
                END,
                CASE WHEN donnees_techniques.su2_cstr_shon6 IS NULL
                    THEN ''
                    ELSE CONCAT ('Restauration - ', donnees_techniques.su2_cstr_shon6, ' m² / ')
                END,
                CASE WHEN donnees_techniques.su2_cstr_shon7 IS NULL
                    THEN ''
                    ELSE CONCAT ('Commerce de gros - ', donnees_techniques.su2_cstr_shon7, ' m² / ')
                END,
                CASE WHEN donnees_techniques.su2_cstr_shon8 IS NULL
                    THEN ''
                    ELSE CONCAT ('Activités de services où s''effectue l''accueil d''une clientèle - ', donnees_techniques.su2_cstr_shon8, ' m² / ')
                END,
                CASE WHEN donnees_techniques.su2_cstr_shon9 IS NULL
                    THEN ''
                    ELSE CONCAT ('Hébergement hôtelier et touristique - ', donnees_techniques.su2_cstr_shon9, ' m² / ')
                END,
                CASE WHEN donnees_techniques.su2_cstr_shon10 IS NULL
                    THEN ''
                    ELSE CONCAT ('Cinéma - ', donnees_techniques.su2_cstr_shon10, ' m² / ')
                END,
                CASE WHEN donnees_techniques.su2_cstr_shon21 IS NULL
                    THEN ''
                    ELSE CONCAT ('Hôtels - ', donnees_techniques.su2_cstr_shon21, ' m² / ')
                END,
                CASE WHEN donnees_techniques.su2_cstr_shon22 IS NULL
                    THEN ''
                    ELSE CONCAT ('Autres hébergements touristiques - ', donnees_techniques.su2_cstr_shon22, ' m² / ')
                END,
                CASE WHEN donnees_techniques.su2_cstr_shon11 IS NULL
                    THEN ''
                    ELSE CONCAT ('Locaux et bureaux accueillant du public des administrations publiques et assimilés - ', donnees_techniques.su2_cstr_shon11, ' m² / ')
                END,
                CASE WHEN donnees_techniques.su2_cstr_shon12 IS NULL
                    THEN ''
                    ELSE CONCAT ('Locaux techniques et industriels des administrations publiques et assimilés - ', donnees_techniques.su2_cstr_shon12, ' m² / ')
                END,
                CASE WHEN donnees_techniques.su2_cstr_shon13 IS NULL
                    THEN ''
                    ELSE CONCAT ('Établissements d''enseignement, de santé et d''action sociale - ', donnees_techniques.su2_cstr_shon13, ' m² / ')
                END,
                CASE WHEN donnees_techniques.su2_cstr_shon14 IS NULL
                    THEN ''
                    ELSE CONCAT ('Salles d''art et de spectacles - ', donnees_techniques.su2_cstr_shon14, ' m² / ')
                END,
                CASE WHEN donnees_techniques.su2_cstr_shon15 IS NULL
                    THEN ''
                    ELSE CONCAT ('Équipements sportifs - ', donnees_techniques.su2_cstr_shon15, ' m² / ')
                END,
                CASE WHEN donnees_techniques.su2_cstr_shon23 IS NULL
                    THEN ''
                    ELSE CONCAT ('Lieux de culte - ', donnees_techniques.su2_cstr_shon23, ' m² / ')
                END,
                CASE WHEN donnees_techniques.su2_cstr_shon16 IS NULL
                    THEN ''
                    ELSE CONCAT ('Autres équipements recevant du public - ', donnees_techniques.su2_cstr_shon16, ' m² / ')
                END,
                CASE WHEN donnees_techniques.su2_cstr_shon17 IS NULL
                    THEN ''
                    ELSE CONCAT ('Industrie - ', donnees_techniques.su2_cstr_shon17, ' m² / ')
                END,
                CASE WHEN donnees_techniques.su2_cstr_shon18 IS NULL
                    THEN ''
                    ELSE CONCAT ('Entrepôt - ', donnees_techniques.su2_cstr_shon18, ' m² / ')
                END,
                CASE WHEN donnees_techniques.su2_cstr_shon19 IS NULL
                    THEN ''
                    ELSE CONCAT ('Bureau - ', donnees_techniques.su2_cstr_shon19, ' m² / ')
                END,
                CASE WHEN donnees_techniques.su2_cstr_shon20 IS NULL
                    THEN ''
                    ELSE CONCAT ('Centre de congrès et d''exposition - ', donnees_techniques.su2_cstr_shon20, ' m²')
                END,
                CASE WHEN donnees_techniques.su2_cstr_shon24 IS NULL
                    THEN ''
                    ELSE CONCAT ('Cuisine dédiée à la vente en ligne - ', donnees_techniques.su2_cstr_shon24, ' m²')
                END
            ), ' / $', '')
        ELSE
            REGEXP_REPLACE(CONCAT(
                CASE
                    WHEN donnees_techniques.su_cstr_shon1 IS NULL
                    THEN ''
                    ELSE CONCAT('Habitation - ', donnees_techniques.su_cstr_shon1, ' m² / ')
                END,
                CASE
                    WHEN donnees_techniques.su_cstr_shon2 IS NULL
                    THEN ''
                    ELSE CONCAT('Hébergement hôtelier - ', donnees_techniques.su_cstr_shon2, ' m² / ')
                END,
                CASE
                    WHEN donnees_techniques.su_cstr_shon3 IS NULL
                    THEN ''
                    ELSE CONCAT('Bureaux - ', donnees_techniques.su_cstr_shon3, ' m² / ')
                END,
                CASE
                    WHEN donnees_techniques.su_cstr_shon4 IS NULL
                    THEN ''
                    ELSE CONCAT('Commerce - ', donnees_techniques.su_cstr_shon4, ' m² / ')
                END,
                CASE
                    WHEN donnees_techniques.su_cstr_shon5 IS NULL
                    THEN ''
                    ELSE CONCAT('Artisanat - ', donnees_techniques.su_cstr_shon5, ' m² / ')
                END,
                CASE
                    WHEN donnees_techniques.su_cstr_shon6 IS NULL
                    THEN ''
                    ELSE CONCAT('Industrie - ', donnees_techniques.su_cstr_shon6, ' m² / ')
                END,
                CASE
                    WHEN donnees_techniques.su_cstr_shon7 IS NULL
                    THEN ''
                    ELSE CONCAT('Exploitation agricole ou forestière - ', donnees_techniques.su_cstr_shon7, ' m² / ')
                END,
                CASE
                    WHEN donnees_techniques.su_cstr_shon8 IS NULL
                    THEN ''
                    ELSE CONCAT('Entrepôt - ', donnees_techniques.su_cstr_shon8, ' m² / ')
                END, 
                CASE
                    WHEN donnees_techniques.su_cstr_shon9 IS NULL
                    THEN ''
                    ELSE CONCAT('Service public ou d''intérêt collectif - ', donnees_techniques.su_cstr_shon9, ' m²')
                END
            ), ' / $', '')
    END as tab_surface_donnees_techniques,
    donnees_techniques.co_tot_log_nb as co_tot_log_nb_donnees_techniques,
    donnees_techniques.co_statio_place_nb as co_statio_place_nb_donnees_techniques,
    -- Si une valeur est saisie dans la deuxième version du tableau des surfaces
    -- alors on récupère seulement ses valeurs
    CASE WHEN su2_avt_shon1 IS NOT NULL
        OR su2_avt_shon2 IS NOT NULL
        OR su2_avt_shon3 IS NOT NULL
        OR su2_avt_shon4 IS NOT NULL
        OR su2_avt_shon5 IS NOT NULL
        OR su2_avt_shon6 IS NOT NULL
        OR su2_avt_shon7 IS NOT NULL
        OR su2_avt_shon8 IS NOT NULL
        OR su2_avt_shon9 IS NOT NULL
        OR su2_avt_shon10 IS NOT NULL
        OR su2_avt_shon11 IS NOT NULL
        OR su2_avt_shon12 IS NOT NULL
        OR su2_avt_shon13 IS NOT NULL
        OR su2_avt_shon14 IS NOT NULL
        OR su2_avt_shon15 IS NOT NULL
        OR su2_avt_shon16 IS NOT NULL
        OR su2_avt_shon17 IS NOT NULL
        OR su2_avt_shon18 IS NOT NULL
        OR su2_avt_shon19 IS NOT NULL
        OR su2_avt_shon20 IS NOT NULL
        OR su2_avt_shon21 IS NOT NULL
        OR su2_avt_shon22 IS NOT NULL
        OR su2_avt_shon23 IS NOT NULL
        OR su2_avt_shon24 IS NOT NULL
        OR su2_cstr_shon1 IS NOT NULL
        OR su2_cstr_shon2 IS NOT NULL
        OR su2_cstr_shon3 IS NOT NULL
        OR su2_cstr_shon4 IS NOT NULL
        OR su2_cstr_shon5 IS NOT NULL
        OR su2_cstr_shon6 IS NOT NULL
        OR su2_cstr_shon7 IS NOT NULL
        OR su2_cstr_shon8 IS NOT NULL
        OR su2_cstr_shon9 IS NOT NULL
        OR su2_cstr_shon10 IS NOT NULL
        OR su2_cstr_shon11 IS NOT NULL
        OR su2_cstr_shon12 IS NOT NULL
        OR su2_cstr_shon13 IS NOT NULL
        OR su2_cstr_shon14 IS NOT NULL
        OR su2_cstr_shon15 IS NOT NULL
        OR su2_cstr_shon16 IS NOT NULL
        OR su2_cstr_shon17 IS NOT NULL
        OR su2_cstr_shon18 IS NOT NULL
        OR su2_cstr_shon19 IS NOT NULL
        OR su2_cstr_shon20 IS NOT NULL
        OR su2_cstr_shon21 IS NOT NULL
        OR su2_cstr_shon22 IS NOT NULL
        OR su2_cstr_shon23 IS NOT NULL
        OR su2_cstr_shon24 IS NOT NULL
        OR su2_chge_shon1 IS NOT NULL
        OR su2_chge_shon2 IS NOT NULL
        OR su2_chge_shon3 IS NOT NULL
        OR su2_chge_shon4 IS NOT NULL
        OR su2_chge_shon5 IS NOT NULL
        OR su2_chge_shon6 IS NOT NULL
        OR su2_chge_shon7 IS NOT NULL
        OR su2_chge_shon8 IS NOT NULL
        OR su2_chge_shon9 IS NOT NULL
        OR su2_chge_shon10 IS NOT NULL
        OR su2_chge_shon11 IS NOT NULL
        OR su2_chge_shon12 IS NOT NULL
        OR su2_chge_shon13 IS NOT NULL
        OR su2_chge_shon14 IS NOT NULL
        OR su2_chge_shon15 IS NOT NULL
        OR su2_chge_shon16 IS NOT NULL
        OR su2_chge_shon17 IS NOT NULL
        OR su2_chge_shon18 IS NOT NULL
        OR su2_chge_shon19 IS NOT NULL
        OR su2_chge_shon20 IS NOT NULL
        OR su2_chge_shon21 IS NOT NULL
        OR su2_chge_shon22 IS NOT NULL
        OR su2_chge_shon23 IS NOT NULL
        OR su2_chge_shon24 IS NOT NULL
        OR su2_demo_shon1 IS NOT NULL
        OR su2_demo_shon2 IS NOT NULL
        OR su2_demo_shon3 IS NOT NULL
        OR su2_demo_shon4 IS NOT NULL
        OR su2_demo_shon5 IS NOT NULL
        OR su2_demo_shon6 IS NOT NULL
        OR su2_demo_shon7 IS NOT NULL
        OR su2_demo_shon8 IS NOT NULL
        OR su2_demo_shon9 IS NOT NULL
        OR su2_demo_shon10 IS NOT NULL
        OR su2_demo_shon11 IS NOT NULL
        OR su2_demo_shon12 IS NOT NULL
        OR su2_demo_shon13 IS NOT NULL
        OR su2_demo_shon14 IS NOT NULL
        OR su2_demo_shon15 IS NOT NULL
        OR su2_demo_shon16 IS NOT NULL
        OR su2_demo_shon17 IS NOT NULL
        OR su2_demo_shon18 IS NOT NULL
        OR su2_demo_shon19 IS NOT NULL
        OR su2_demo_shon20 IS NOT NULL
        OR su2_demo_shon21 IS NOT NULL
        OR su2_demo_shon22 IS NOT NULL
        OR su2_demo_shon23 IS NOT NULL
        OR su2_demo_shon24 IS NOT NULL
        OR su2_sup_shon1 IS NOT NULL
        OR su2_sup_shon2 IS NOT NULL
        OR su2_sup_shon3 IS NOT NULL
        OR su2_sup_shon4 IS NOT NULL
        OR su2_sup_shon5 IS NOT NULL
        OR su2_sup_shon6 IS NOT NULL
        OR su2_sup_shon7 IS NOT NULL
        OR su2_sup_shon8 IS NOT NULL
        OR su2_sup_shon9 IS NOT NULL
        OR su2_sup_shon10 IS NOT NULL
        OR su2_sup_shon11 IS NOT NULL
        OR su2_sup_shon12 IS NOT NULL
        OR su2_sup_shon13 IS NOT NULL
        OR su2_sup_shon14 IS NOT NULL
        OR su2_sup_shon15 IS NOT NULL
        OR su2_sup_shon16 IS NOT NULL
        OR su2_sup_shon17 IS NOT NULL
        OR su2_sup_shon18 IS NOT NULL
        OR su2_sup_shon19 IS NOT NULL
        OR su2_sup_shon20 IS NOT NULL
        OR su2_sup_shon21 IS NOT NULL
        OR su2_sup_shon22 IS NOT NULL
        OR su2_sup_shon23 IS NOT NULL
        OR su2_sup_shon24 IS NOT NULL
        THEN donnees_techniques.su2_tot_shon_tot
        ELSE donnees_techniques.su_tot_shon_tot
    END as su_tot_shon_tot_donnees_techniques,

    -- Données techniques pour les AT
    CONCAT_WS(', ',
        CASE WHEN donnees_techniques.erp_cstr_neuve IS TRUE THEN 'construction neuve' END,
        CASE WHEN donnees_techniques.erp_trvx_acc IS TRUE THEN 'travaux de mise en conformité totale aux règles d’accessibilité' END,
        CASE WHEN donnees_techniques.erp_extension IS TRUE THEN 'extension' END,
        CASE WHEN donnees_techniques.erp_rehab IS TRUE THEN 'réhabilitation' END,
        CASE WHEN donnees_techniques.erp_trvx_am IS TRUE THEN 'travaux d’aménagement (remplacement de revêtements, rénovation électrique, création d’une rampe, par exemple)' END,
        CASE WHEN donnees_techniques.erp_vol_nouv_exist IS TRUE THEN 'création de volumes nouveaux dans des volumes existants (modification du cloisonnement, par exemple)' END
    ) as at_type_travaux,
    donnees_techniques.erp_public_eff_tot as at_effectif_public_total,
    CONCAT_WS(' - ', erp_categorie.libelle, erp_categorie.description) as at_categorie_etablissement,
    CONCAT_WS(' - ', erp_type.libelle, erp_type.description) as at_type_etablissement,

    -- Données techniques pour les DPC
    donnees_techniques.dpc_type as dpc_type,
    donnees_techniques.dpc_moda_cess_prix as dpc_moda_cess_prix
    
FROM 

    &DB_PREFIXEconsultation 
    LEFT JOIN &DB_PREFIXEservice 
        ON service.service=consultation.service 
    LEFT JOIN &DB_PREFIXEdossier 
        ON dossier.dossier=consultation.dossier 
    LEFT JOIN &DB_PREFIXEdonnees_techniques 
        ON dossier.dossier = donnees_techniques.dossier_instruction
    LEFT JOIN &DB_PREFIXEdossier_autorisation 
        ON dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation 
    LEFT JOIN &DB_PREFIXEinstructeur 
        ON instructeur.instructeur=dossier.instructeur
    LEFT JOIN &DB_PREFIXEom_utilisateur as instructeur_utilisateur
        ON instructeur_utilisateur.om_utilisateur=instructeur.om_utilisateur
    LEFT JOIN &DB_PREFIXEinstruction
        ON dossier.dossier=instruction.dossier 
    LEFT JOIN &DB_PREFIXElien_dossier_demandeur 
        ON lien_dossier_demandeur.dossier=dossier.dossier 
    LEFT JOIN &DB_PREFIXEdemandeur 
        ON demandeur.demandeur=lien_dossier_demandeur.demandeur AND lien_dossier_demandeur.petitionnaire_principal IS TRUE
    LEFT JOIN &DB_PREFIXEcivilite 
        ON demandeur.personne_morale_civilite=civilite.civilite OR demandeur.particulier_civilite=civilite.civilite
    LEFT JOIN &DB_PREFIXEdemandeur as petitionnaire_principal
        ON lien_dossier_demandeur.demandeur = petitionnaire_principal.demandeur AND lien_dossier_demandeur.petitionnaire_principal IS TRUE
    LEFT JOIN &DB_PREFIXEcivilite as petitionnaire_principal_civilite
        ON petitionnaire_principal.personne_morale_civilite=petitionnaire_principal_civilite.civilite OR petitionnaire_principal.particulier_civilite=petitionnaire_principal_civilite.civilite
    LEFT JOIN &DB_PREFIXEdossier_instruction_type 
        ON dossier.dossier_instruction_type=dossier_instruction_type.dossier_instruction_type 
    LEFT JOIN &DB_PREFIXEdossier_autorisation_type_detaille 
        ON dossier_instruction_type.dossier_autorisation_type_detaille=dossier_autorisation_type_detaille.dossier_autorisation_type_detaille
    LEFT JOIN
        &DB_PREFIXEerp_categorie
        ON
            donnees_techniques.erp_class_cat = erp_categorie.erp_categorie
    LEFT JOIN
        &DB_PREFIXEerp_type
        ON
            donnees_techniques.erp_class_type = erp_type.erp_type
    LEFT JOIN
        &DB_PREFIXEtaxe_amenagement
        ON
            dossier.om_collectivite = taxe_amenagement.om_collectivite
WHERE consultation.consultation = &idx AND lien_dossier_demandeur.petitionnaire_principal IS TRUE