select distinct	af.num_manzana AS manzana,
					af.num_predial AS predio,
					CONCAT(af.num_manzana, '-', af.num_predial) as rol,
					af.descripcion_comuna,
					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
					 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  from roles_no_agricolas rr inner join codigos_comunas co on rr.id_comuna = co.id_comuna  left join codigos_destinos cd on rr.id_destino = cd.id_destino  where 1 = 1   and rr.id_comuna = '8403'  and replace(replace(rr.direccion_predio, '-', ' '), '/', '') like '%ST 3%' and rr.num_manzana = '321' and rr.num_predial = '37'  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  from roles_agricolas rr inner join codigos_comunas co on rr.id_comuna = co.id_comuna  left join codigos_destinos cd on rr.id_destino = cd.id_destino  where 1 = 1   and rr.id_comuna = '8403'  and replace(replace(rr.direccion_predio, '-', ' '), '/', '') like '%ST 3%' and rr.num_manzana = '321' and rr.num_predial = '37' ) 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 ;<table class="borde-tabla">
	<thead>
		<tr>
			<th class="t-50 left-tabla bold" >Direccion no encontrada</th>			
		</tr>
	</thead>
</table>