none
Erro no UNION RRS feed

  • Pergunta

  •  

    Bom dia colegas!

    Estou fazendo uma quey que resgata os vendedores e a soma de produtos vendidos de cada um (em R$).

    Só que preciso inserir uma linha no final da query, pra exibir o total de todos os vendedores listados.

    Estou usando o UNION e apresenta o seguinte erro:

    Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

     

    A query está abaixo:

     

    SELECT ((SELECT T1.SlpName AS Vendedor, SUM(T0.DocTotal) as teste

    FROM OINV T0 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode INNER JOIN INV1 T2

    ON T0.DocEntry = T2.DocEntry

    WHERE T0.DocDate >= '20080418' AND T0.DocDate <= '20080418' AND T2.LineStatus = 'O'

    GROUP BY T1.SlpName

    --ORDER BY SUM(T0.DocTotal) desc

    )

    UNION

    (Select T1.SlpName, SUM(T0.DocTotal) as Soma

    FROM OINV T0))

     

    O resultado esperado seria :

     

    Carlos

    10.000,00

    Alberto

    8.400,00

    Luis

    6.000,00

    Amélia

    3.000,00

     

    27.400,00

    Desde já agradeço.

    sexta-feira, 18 de abril de 2008 14:07

Respostas

  •  

    Code Snippet

    SELECT * from

    (SELECT T1.SlpName AS Vendedor, SUM(T0.DocTotal) as Soma

    FROM OINV T0 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode INNER JOIN INV1 T2

    ON T0.DocEntry = T2.DocEntry

    WHERE T0.DocDate >= '20080418' AND T0.DocDate <= '20080418' AND T2.LineStatus = 'O'

    GROUP BY T1.SlpName

     

    UNION

    Select ' ' as SlpName, SUM(T0.DocTotal) as Soma

    FROM OINV T0

    ) a

     

    ORDER BY a.soma desc

     

     

     

    sexta-feira, 18 de abril de 2008 14:50

Todas as Respostas

  •  

    segue uma alteranativa

     


    Select SlpName, teste
    From (

    SELECT T1.SlpName AS Vendedor, SUM(T0.DocTotal) as teste
    FROM OINV T0 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode INNER JOIN INV1 T2
    ON T0.DocEntry = T2.DocEntry
    WHERE T0.DocDate >= '20080418' AND T0.DocDate <= '20080418' AND T2.LineStatus = 'O'
    GROUP BY T1.SlpName
    UNION ALL
    Select 'Z-Total', SUM(T0.DocTotal) as Soma FROM OINV T0

    ) Tbl Order By SlpName

     

     

     

    Abs/;

    sexta-feira, 18 de abril de 2008 14:20
  •  

    Riian,

     

    Tire os parenteses

     

    SELECT * from

    (SELECT T1.SlpName AS Vendedor, SUM(T0.DocTotal) as teste

    FROM OINV T0 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode INNER JOIN INV1 T2

    ON T0.DocEntry = T2.DocEntry

    WHERE T0.DocDate >= '20080418' AND T0.DocDate <= '20080418' AND T2.LineStatus = 'O'

    GROUP BY T1.SlpName

     

    UNION

    Select ' ' as SlpName, SUM(T0.DocTotal) as Soma

    FROM OINV T0

    ) a

     

     

    abs

     

    Rafael Krisller

    sexta-feira, 18 de abril de 2008 14:22
  • Bom dia,

     

    Senhores, muito obrigado pela ajuda.

    Essa solução dada pelo Rafael, trouxe o resultado esperado.

    Agora só outra pergunta .... 

    Gostaria de ordenar de forma decrescente os valores, colokei um order by mas dá o seguinte o erro:

     

    Msg 1033, Level 15, State 1, Line 16

    The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

    Msg 102, Level 15, State 1, Line 22

    Incorrect syntax near ')'.

     

     

    A query que eu alterei foi essa: (A linha alterada está em negrito e sublinhada)

     

    SELECT * from

    (SELECT T1.SlpName AS Vendedor, SUM(T0.DocTotal) as teste

    FROM OINV T0 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode INNER JOIN INV1 T2

    ON T0.DocEntry = T2.DocEntry

    WHERE T0.DocDate >= '20080418' AND T0.DocDate <= '20080418' AND T2.LineStatus = 'O'

    GROUP BY T1.SlpName

    ORDER BY SUM(T0.DocTotal) desc

    UNION

    Select ' ' as SlpName, SUM(T0.DocTotal) as Soma

    FROM OINV T0

    ) a

     

    Mais uma vez, obrigado!

     

    Att,

     

    sexta-feira, 18 de abril de 2008 14:30
  • Riian,

     

    Coloque o Order by fora do union, ou seja, no select principal

     

    abs

    sexta-feira, 18 de abril de 2008 14:49
  •  

    Code Snippet

    SELECT * from

    (SELECT T1.SlpName AS Vendedor, SUM(T0.DocTotal) as Soma

    FROM OINV T0 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode INNER JOIN INV1 T2

    ON T0.DocEntry = T2.DocEntry

    WHERE T0.DocDate >= '20080418' AND T0.DocDate <= '20080418' AND T2.LineStatus = 'O'

    GROUP BY T1.SlpName

     

    UNION

    Select ' ' as SlpName, SUM(T0.DocTotal) as Soma

    FROM OINV T0

    ) a

     

    ORDER BY a.soma desc

     

     

     

    sexta-feira, 18 de abril de 2008 14:50
  • Rafael,

     

    Deu certo!

    Mto obrigado pelo help!!

     

    Abs

    sexta-feira, 18 de abril de 2008 15:03