Analizando el caso detectamos que en la query del script, estas VNO se encuentran mencionadas en el "case" pero no en el "where" de la query:
with t_reporte as (
select
distinct (bi.access_id) as ACCESO,
case
when cast(bi.operatorid as varchar)='962' then 'SION TLS - 962'
when cast(bi.operatorid as varchar)='963' then 'SION Residencial - 963'
when cast(bi.operatorid as varchar)='1001' then 'TASA - 1001'
when cast(bi.operatorid as varchar)='2800' then 'Munip Escobar - 2800'
when cast(bi.operatorid as varchar)='2805' then 'Munip S Martin - 2805'
when cast(bi.operatorid as varchar)='2806' then 'Munip S Fernando - 2806'
when cast(bi.operatorid as varchar)='3001' then 'DTV - 3001'
when cast(bi.operatorid as varchar)='3950' then 'IPLAN - 3950'
when cast(bi.operatorid as varchar)='4000' then 'Metrotel - 4000'
else
cast(bi.operatorid as varchar)
end
as VNO,
case
when (s.serial_number not like 'ALCL0%' and s.serial_number is not null) then 'IN SERVICE'
else
'RESERVED'
end
as ESTADO,
bi.provided_date as FECHA_PROVISION,
csmb.partido_despliegue as PARTIDO_GEOGRAFICO_ONT,
csmb.cabecera as CABECERA,
bi.cable_puerta_olt as ALIMENTADOR,
bi.object_name as OBJECT_NAME
from
aux.bajada_inventario bi
join altiplano.serial s on
(split_part(s.object_name,'-',1)||':1-1-'||split_part(s.object_name,'-',2)||'-'||split_part(s.object_name,'-',3)||'-'||replace(split_part(s.object_name,'-',4),'_GPON','')) = bi.object_name
join cm.ci_sfat_mfat_bfat csmb
on
bi.cto = csmb.nfctag
where csmb.topologia ='G30'
and (
(bi.operatorid='1001' and s.vno='1001')
or (bi.operatorid='4000' and s.vno='4000')
or (bi.operatorid='3950' and s.vno='3950')
or (bi.operatorid='962' and s.vno='962')
or (bi.operatorid='1000' and s.vno='1000')
or (bi.operatorid='3001' and s.vno='3001')
)
and s.serial_number !=''
),
t_bws as (
select s.access_id , string_agg(concat('', pb."shaper-name"), '|') as BW from altiplano.perfil_bajada pb
join altiplano.serial s on s.object_name = pb."object-name"
---where (not pb."shaper-name" like '%GESTION%') and (not pb."shaper-name" like '%1kb%')
group by s.access_id
)
select * from t_reporte left join t_bws on t_bws.access_id=t_reporte.acceso
Esto hizo que analizara la tabla altiplano.serial, en la que efectivamente esos valores no existen.
