SELECT  'Commune &commune'||' '||'Dossier '||dossier as dossier,
 'Depot '||to_char(date_depot,'DD/MM/YYYY')||' Notifie le  '||COALESCE(to_char(date_complet,'DD/MM/YYYY'),'inconu') as date_dp_n,
COALESCE(demandeur_civilite,'sans')||' '||demandeur_nom||' '||demandeur_adresse||' '||demandeur_cp||'  '||demandeur_ville||' Parcelle '||parcelle as nom_adresse_demandeur,
terrain_numero||' '||terrain_numero_complement||' '||terrain_adresse||' '||terrain_adresse_complement||'
 '||terrain_cp||'  '||terrain_ville||' '||travaux.libelle as adresse_terrain_travaux, 
'shon  '||shon||' shob '||shob as SN_SB, 
terrain_surface as superficie, 
logement_nombre as nbr_logement, 
COALESCE(avis.libelle,'inconu') as avis,
'Decision'||COALESCE(to_char(date_decision,'DD/MM/YYYY'),'inconu')||' Limite '||COALESCE(to_char(date_limite,'DD/MM/YYYY'),'inconu') as date_dc_l,
delai||' mois' as delai, ' ' as date_affichage_decision, ' ' as DOC_DAT_Conformite
from dossier left join travaux on dossier.travaux=travaux.travaux left join avis on dossier.avis=avis.avis 
 where dossier.nature='&nature' AND (date_decision>='&datedebut'
 AND date_decision<='&datefin')
ORDER BY dossier