select [Numéro identification ERP]=l.c_local,
[Société]=s.noma_soc,
[Agence]=c.c_centre_pere+substring(a.c_postal,1,2),
[Libellé agence]=c1.lib,
[Groupe]=l.c_prog,
[Libellé Groupe]=p.lib,
[Immeuble]=l.c_bat,
[Libellé Immeuble]=l.c_bat,
[Adresse]=CASE WHEN TRIM(a2.n_voie)='.' THEN '' ELSE TRIM(a2.n_voie)+' ' END+CASE WHEN TRIM(a2.t_voie)='.' THEN '' ELSE TRIM(a2.t_voie)+' ' END+CASE WHEN TRIM(a2.lib)='.' THEN TRIM(a2.compl_adr) WHEN TRIM(a2.lib)='' THEN TRIM(a2.compl_adr) ELSE TRIM(a2.lib) END,
[Code INSEE]=a.c_insee,
[Code Postal]=a.c_postal,
[Niveau]=l.etage,
[Escalier]=l.c_esc,
[Bâtiment]=l.c_bat,
[Usage du local]=(SELECT lib FROM t_stat_local tsl1 WHERE tsl1.c_type=(SELECT t_stat FROM t_local tl1 WHERE l.type=tl1.c_type)),
[Nature Local]=(SELECT lib FROM usage_bail ub1 WHERE l.usa_stand=ub1.c_usage),
/*[Nature Local]=CASE WHEN l.usa_stand IN ('L1') THEN 'Habitation conventionné'
	WHEN l.usa_stand IN ('L2') THEN 'Habitation non conventionnée'
	WHEN l.usa_stand IN ('CO') THEN 'Commerce'
	WHEN l.usa_stand IN ('LF') THEN 'Foyer'
	WHEN l.usa_stand IN ('AN') THEN 'Annexe soumis TVA'
	WHEN l.usa_stand IN ('ST') THEN 'Stationnement ou Parking'
	WHEN l.usa_stand IN ('PR') THEN 'Prêt'
	WHEN l.usa_stand IN ('RE') THEN 'Foncier'
	END,*/
[Type de local]=(SELECT lib FROM t_local tl1 WHERE l.type=tl1.c_type),
[QPV]=CASE WHEN l.qpv IN ('','2') then '0' WHEN l.qpv='1' THEN '1' END,
[MES]=l.dat_patrim,
[Fin MES]=l.dat_cession,
[DAT]=p.d_constr,
[Invariant]=l.invariant,
[Surface habitable]=convert(int,floor(l.surf_bail)),
[Surface corrigée]=convert(int,floor(l.surf_corrige)),
[Surface utile]=convert(int,floor(l.surf_corrige)),
[Etat de gestion]=case 
	when l.mode_sortie in ('1','2','3') then 'Vendu'
	when l.mode_sortie='4' then 'Démolis'
	else 'Location'
	end,
[Numéro de local]=l.c_local,
[Porte]=l.porte,
[Date octroi du prêt]='',
[Date de conventionnement]=(SELECT p1.d_deb FROM prog_convention p1 WHERE p1.c_soc=l.c_soc AND p1.c_prog=l.c_prog AND p1.c_conv=l.c_conv),
[Conventionnement]=CASE WHEN (SELECT p1.conventionne FROM prog_convention p1 WHERE p1.c_soc=l.c_soc AND p1.c_prog=l.c_prog AND p1.c_conv=l.c_conv)='O' THEN '1' ELSE '0' END,
[Collectif/individuel]=case l.t_const when 'C' then 'Collectif' else 'Individuel' end,
[Nom commune]=TRIM(a2.bur_dist),
[Fin exo]='',
[Valeur locative]='',
[QPV Libellé]='',
[Nature Gel]='',
[Début Gel]='',
[Fin Gel]='',
[Nature de vacance]='',
[Début de vacance]='',
[Fin de vacance]='',
[Nombre de mois consécutif de vacance]='',
[SIE]='',
[Famille Logement]='',
[Pièces principales]='',
[Salles à manger]='',
[Chambres]='',
[Cuisine ]='',
[Salle d''eau]='',
[Pièces annexes]='',
[Eau ]='',
[Electricité]='',
[Gaz]='',
[Ascenseur]='',
[Chauffage central]='',
[Vide-ordures]='',
[Egout]='',
[Baignoires]='',
[Douches]='',
[Lavabos]='',
[WC]='',
[Dépendance 1]='',
[Dépendance 2]='',
[Dépendance 3]='',
[Dépendance 4]='',
[Longitude]='',
[Lattitude]='',
[Date octroi de subvention]='',
[Type de financement]='',
[Nom de l''occupant]='',
[Préfixe]='',
[Section cadastrale]='',
[Numéro de plan]='',
[Date de dernière réhabilitation]='',
[Type d''acquisition]='',
[Champ libre 1]='',
[Champ libre 2]='',
[Champ libre 3]='',
[Champ libre 4]='',
[Champ libre 5]='',
[Champ libre 6]='',
[Champ libre 7]='',
[Champ libre 8]='',
[Champ libre 9]='',
[Champ libre 10]='',
[DPE]='',
[Equivalent logement]=''

from soc s, param pa, prog p,centre c, adr_patrim a, adr_patrim a2,centre c1, local l 
left outer join local_bail b on d_fin_occup = '' and b.c_soc = l.c_soc and b.c_local = l.c_local

where s.c_soc = SOC and l.c_soc = SOC and p.c_soc = SOC and p.c_prog = l.c_prog
and c.c_soc = SOC and c.c_centre = p.c_prog
and a.c_soc = SOC and a.c_prog=l.c_prog and a.c_bat = l.c_bat and a.c_esc=l.c_esc and a.etage= l.etage and a.porte=l.porte
and a2.c_soc = SOC and a2.c_prog=l.c_prog and a2.c_bat = l.c_bat and a2.c_esc=l.c_esc and a2.etage= l.etage and a2.porte=l.porte
and c1.c_soc = SOC and c1.c_centre = c.c_centre_pere 
and c.c_centre_pere!='U03'

ORDER BY l.c_prog, l.c_local