Values of the IN clause as a parameter for in a column subselect

Asked

Viewed 63 times

1

I need to pass all the values of a clause IN, for a subselect one-column.

Ex:

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 appointments. No where of subselect (column with the alias "total"), I compare the competence with the competence of the select external, and the sector with the external select sector. So far, everything normal. The problem is the provider, who, if there was only one, would just stay the way it is up there. However, I need the comparison to be made with all the values that are passed in the clause IN of the external select.

Thanks in advance.

sql oracle11g

  • His image editing of the code impoverished the publication, so I just formatted and identei his query

  • If I understood the problem I would just exchange in subselect the cd_provider = for a replication of the "in" ... the aggregator will be for the total.

  • Yes. Doing the query in an editor, it would work. But the query goes to a report, you can’t keep these fixed numbers.

  • https://forum.imasters.com.br/topic/289781-concatenar-valores/? do=findComment&comment=1053697 see if it helps ... but your problem is the same with a single query...

1 answer

0

Maybe so?

SELECT      DISTINCT 'Atendimentos'                 AS Tipo
        ,   TO_CHAR(A.DT_ATENDIMENTO, 'mm/yyyy')    AS Competencia
        ,   PRE.CD_PRESTADOR
        ,   SE.CD_SETOR
        ,   SE.NM_SETOR
        ,   NVL(X.Contador, 0)                      AS 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
LEFT JOIN   (
                SELECT      PRE.CD_PRESTADOR
                        ,   SE.CD_SETOR
                        ,   TO_CHAR(A.DT_ATENDIMENTO, 'mm/yyyy')    AS Data
                        ,   COUNT(DISTINCT A.CD_ATENDIMENTO)        AS Contador
                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
                GROUP BY    PRE.CD_PRESTADOR
                        ,   SE.CD_SETOR
                        ,   TO_CHAR(A.DT_ATENDIMENTO, 'mm/yyyy')
            ) X             ON  X.CD_PRESTADOR  = A.CD_PRESTADOR
                            AND X.CD_SETOR      = SE.CD_SETOR
                            AND X.Data          = TO_CHAR(A.DT_ATENDIMENTO, 'mm/yyyy')
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

Browser other questions tagged sql oracle11g

You are not signed in. Login or sign up in order to post.