select etablissement.nom as nom, etablissement.prenom as prenom ,type_etablissement.libelle as type_etab,etablissement.raison_sociale as nometab,
etablissement.numero_voie as adr1 , etablissement.complement as adr2, etablissement.libelle_voie as adr3, occupation.quantite as quantite, occupation.unite as unite,
to_char(occupation.debutvalidite, 'DD/MM/YYYY') as dat1, to_char(occupation.finvalidite,'DD/MM/YYYY') as dat2
from  &DB_PREFIXEoccupation inner join &DB_PREFIXEetablissement on etablissement.etablissement=occupation.etablissement left join &DB_PREFIXEtype_etablissement on etablissement.type_etablissement=type_etablissement.type_etablissement where occupation.occupation=&idx