CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Vinicius Masson
Vinicius Masson

Reputation: 3

add all values of a IN clause to an subselect column

I need to pass all values from an IN clause, to asubselect from a column.

SELECT   distinct
         'Atendimentos' Tipo,
         to_char(A.DT_ATENDIMENTO, 'mm/yyyy') Competencia,
         pre.cd_prestador,
         se.cd_setor,
         se.nm_setor,
         (
            select  (count(distinct a2.cd_atendimento)) --* 0.3
            from    atendime a2
                    INNER JOIN PRESTADOR Pre2 ON A2.CD_PRESTADOR = 
                                         Pre2.CD_PRESTADOR
                    left join setor se2 on se2.CD_SETOR = 
                                         a2.cd_setor
            where   to_char(a2.DT_ATENDIMENTO, 'mm/yyyy') = 
                    to_char(a.DT_ATENDIMENTO, 'mm/yyyy')
                    and pre2.cd_prestador = pre.cd_prestador --Aqui é o problema
                    and se2.cd_setor = se.cd_setor
            group by to_char(A.DT_ATENDIMENTO, 'mm/yyyy')
         ) total
FROM     ATENDIME A
         INNER JOIN PRESTADOR Pre ON A.CD_PRESTADOR = Pre.CD_PRESTADOR
         left join setor se on se.CD_SETOR = a.CD_SETOR
WHERE    to_char(A.DT_ATENDIMENTO, 'mm/yyyy') = '08/2018'
         and pre.cd_prestador in (127,3921,1704,28,5048,3118,130,3336,2171,1869,128,132,186)
order by se.cd_setor

I need this last column to bring me the overall total of calls.

In the subselect WHERE (column with the "total" alias), I compare the competency with the external select competency, and the sector with the external select sector.

So far, everything normal.

The problem is the column prestador, which is, if only one, just had to stay that way up there.

However, I need the comparison to be made with all values that are passed in the IN clause of the external select.

Upvotes: 0

Views: 40

Answers (1)

GMB
GMB

Reputation: 222432

I understand that the subquery is meant to compute a total over the whole result set. If so, then this looks like a typical use case for window functions.

I suspect that your subquery could be replaced with just a window function, as follows :

SELECT   distinct
         'Atendimentos' Tipo,
         to_char(A.DT_ATENDIMENTO, 'mm/yyyy') Competencia,
         pre.cd_prestador,
         se.cd_setor,
         se.nm_setor,
         count(distinct a2.cd_atendimento) over() total
FROM     ATENDIME A
         INNER JOIN PRESTADOR Pre ON A.CD_PRESTADOR = Pre.CD_PRESTADOR
         left join setor se on se.CD_SETOR = a.CD_SETOR
where    to_char(A.DT_ATENDIMENTO, 'mm/yyyy') = '08/2018'
         and pre.cd_prestador in (127,3921,1704,28,5048,3118,130,3336,2171,1869,128,132,186)
order by se.cd_setor

Upvotes: 1

Related Questions