I'm having trouble filtering display

User_AC3QE

I'm having trouble trying not to display the line 'NOT ISOLATED' when there is more than one item in the "ds_motivo" column, with the same code in the "cd_setor" column and the same date in the "dt_referencia" column

example should only display the first line of the result.

as line 3 has more items in the ds_motivo column and with the same code in the "Cd_setor" column and the same date in the "dt_referencia" column, I don't want that item to be displayed.

result example

01/01/2020 411 Unidade Coronária - DE 07º andar - Ala III does not appear

SQL

with temp as
(select z.dt_referencia as dt_referencia,
                z.cd_setor,
                z.ds_setor,
                sum (decode (z.IE_TIPO, 'DIA', decode (z.ie_status, 'Busy', 1,0), 0)) as qt_paciente_dia
from hsl_censo_bi z
where z.ie_tipo = 'DIA'
and z.dt_referencia between: dt_inicio and: dt_fim
and z.nr_atendimento is not null
and z.cd_establishment = 1
group by z.dt_referencia,
         z.cd_setor,
         z.ds_setor
order by 1)
select t.dt_referencia as dt_referencia,
                t.cd_sector,
                t.ds_setor to ds_setor,
                case when mi.ds_motivo is null then 'NOT ISOLATED'
                else mi.ds_motivo end as ds_motivo,
                t.qt_paciente_dia as qt_paciente_dia,
                count (mi.ds_motivo) as qt_motivo,
                round (((count (mi.ds_motivo) * 100) / t.qt_paciente_dia)) as col
               
from hsl_censo_bi a
left join temp t on a.dt_referencia = t.dt_referencia and a.cd_setor = t.cd_setor
left join ATENDIMENTO_PRECAUCAO APR ON APR.NR_ATENDIMENTO = A.NR_ATENDIMENTO
left join CIH_PRECAUCAO CP ON CP.NR_SEQUENCIA = APR.NR_SEQ_PRECAUCAO
left join MOTIVO_ISOLAMENTO MI ON MI.NR_SEQUENCIA = APR.NR_SEQ_MOTIVO_ISOL
where a.ie_tipo = 'DIA'
and a.dt_referencia between: dt_inicio and: dt_fim
and a.nr_atendimento is not null
and apr.dt_inativacao is null
and a.cd_establishment = 1
and t.cd_setor in (409,410,411)
group by t.dt_referencia,
                t.cd_sector,
                t.ds_sector,
                t.qt_paciente_dia,
                mi.ds_motivo
order by 1,3,4

Frank Kulash

Duplicate of: I'm having trouble filtering display — oracle-tech

Don't start multiple threads for the same problem. See the other thread (and How to Ask Questions in Developer Community Spaces - oracle-tech ) for what you need to post.