select distinct	af.num_manzana AS manzana,
						af.num_predial AS predio,
						CONCAT(af.num_manzana, '-', af.num_predial) as rol,
						CONCAT(af.num_manzana_rol1, '-', af.num_predial_rol1) as rol_condominio,
						lower(af.descripcion_comuna) as descripcion_comuna,
						lower(ltrim(rtrim(af.direccion_predio))) as direccion_predio,
						af.avaluo_fiscal,
						af.avaluo_exento,
						af.id_comuna,
						(af.avaluo_fiscal - af.avaluo_exento) as avaluo_total,
						af.contribucion_semestral,
						af.superficie_total,
						af.id_comuna_rol1,
						af.num_manzana_rol1,
						af.num_predial_rol1,
						/*rr.nombre_propietario,*/
						rr.anio_termino_exencion,
						case when rr.codigo_ubicacion = 'R' then 'Rural' else 'Urbano' end as codigo_ubicacion,
						coalesce(cd.descripcion_destino, af.descripcion_destino) as descripcion_destino,
						descripcion_region			
				from (
					select num_manzana, num_predial, direccion_predio, avaluo_fiscal, avaluo_exento, contribucion_semestral, descripcion_comuna, rr.id_comuna, rr.superficie_total, 
					rr.id_comuna_rol1, rr.num_manzana_rol1, rr.num_predial_rol1, cd.descripcion_destino, re.descripcion_region 
					from roles_no_agricolas rr inner join codigos_comunas co on rr.id_comuna = co.id_comuna inner join codigos_regiones re on co.id_Region = re.id_region 
					left join codigos_destinos cd on rr.id_destino = cd.id_destino 
					 where 1 = 1   and co.descripcion_comuna = 's'  and rr.direccion_predio2 like '%tomas 1679%' and rr.num_manzana = 'n-ber' and rr.num_predial = 'nardo' 
				union 
				select num_manzana, num_predial, direccion_predio, avaluo_fiscal, avaluo_exento, contribucion_semestral, descripcion_comuna, rr.id_comuna, 0 as superficie_total, 
				0 as id_comuna_rol1, 0 as num_manzana_rol1, 0 as num_predial_rol1, cd.descripcion_destino, re.descripcion_region 
				from roles_agricolas rr inner join codigos_comunas co on rr.id_comuna = co.id_comuna inner join codigos_regiones re on co.id_Region = re.id_region  
				left join codigos_destinos cd on rr.id_destino = cd.id_destino 
				 where 1 = 1   and co.descripcion_comuna = 's'  and rr.direccion_predio2 like '%tomas 1679%' and rr.num_manzana = 'n-ber' and rr.num_predial = 'nardo' 
				) af 
				left join roles_propietarios rr on af.id_comuna = rr.id_comuna and af.num_manzana = rr.num_manzana
										and af.num_predial = rr.num_predial 
		left join codigos_destinos cd on rr.id_destino = cd.id_destino