Group By - Apresentando mensagem de erro

SQL

MySQL

Oracle

Firebird

17/12/2021

/*ERRO APRESENTADO

INVALID EXPRESSION IN THE SELECT LIST (NOT CONTAINED IN EITHER AN
AGGREGATE FUNCTION OR THE GROUP BY CALUSE)

*/



SELECT
/*SAIDA*/
GELANSAI.ITEM ITEM_SAIDA,
GECADSAI.DATA DATA_SAIDA,
SUM(GELANSAI.QTDE) QTDE_SAIDA,
SUM(GELANSAI.VLRMED) VLRMED_SAIDA,
GECADSAI.CDC CDC_SAIDA,
/*ITENS*/
I.NOME,
I.UNI_CON,
I.CUSTO VLR_UNI,
(SELECT FIRST 1 GEGRUPOS.NOME FROM GEGRUPOS WHERE GEGRUPOS.COD = I.GRU) GRUPO,
(SELECT FIRST 1 NOME FROM TBCENCUS WHERE COD=GECADSAI.CDC) CDC,
/*DEVOLUCAO*/
GELANDEV.ITEM ITEM_DEV,
GECADDEV.DATA DATA_DEV,
SUM(GELANDEV.QTDE) QTDE_DEV,
SUM(GELANDEV.VLRMED) VLRMED_DEV,
GECADDEV.CDC CDC_DEV

FROM GECADSAI
LEFT JOIN GELANSAI ON GECADSAI.ANO=GELANSAI.ANO
AND GECADSAI.MES=GELANSAI.MES
AND GECADSAI.DOC=GELANSAI.DOC
LEFT JOIN GEITENS I ON GELANSAI.ITEM=I.COD
LEFT JOIN TBCENCUS T ON T.COD=GECADSAI.CDC
LEFT JOIN GELANDEV ON GELANDEV.ID_GELANSAI=GELANSAI.ID_GELANSAI
LEFT JOIN GECADDEV ON GECADDEV.ID=GELANDEV.ID_GECADDEV
WHERE GECADSAI.ANO=:ANO
AND GECADSAI.MES=:MES
AND (
(NOT GECADSAI.CDC IS NULL AND GECADSAI.CDC BETWEEN :CDC1 AND :CDC2)
OR
(NOT GECADDEV.CDC IS NULL AND GECADDEV.CDC BETWEEN :CDC1 AND :CDC2)
)
AND (
(NOT GECADSAI.DATA IS NULL AND GECADSAI.DATA BETWEEN :DATA1 AND :DATA2)
OR
(NOT GECADDEV.DATA IS NULL AND GECADDEV.DATA BETWEEN :DATA1 AND :DATA2)
)
AND (COALESCE(GELANSAI.CONSOL,'')='T' OR COALESCE(GECADSAI.CONSOL,'')='T')
GROUP BY GELANSAI.ITEM,
GECADSAI.DATA,
GELANSAI.QTDE,
GELANSAI.VLRMED,
GECADSAI.CDC,
I.NOME,
I.CUSTO,
GELANDEV.ITEM,
GECADDEV.DATA,
GELANDEV.QTDE,
GELANDEV.VLRMED,
GECADDEV.CDC
ORDER BY CDC,
NOME,
GELANSAI.ITEM,
GECADSAI.DATA,
GELANSAI.QTDE,
GELANSAI.VLRMED,
GECADSAI.CDC,
I.NOME,
I.CUSTO,
GELANDEV.ITEM,
GECADDEV.DATA,
GELANDEV.QTDE,
GELANDEV.VLRMED,
GECADDEV.CDC
Marcos Batista

Marcos Batista

Curtidas 0

Melhor post

Emerson Nascimento

Emerson Nascimento

17/12/2021

você tem o campo
I.UNI_CON
na SELECT, mas não tem na GROUP BY

não faz sentido ter na GROUP BY os campos
GELANSAI.QTDE,
GELANSAI.VLRMED,
GELANDEV.QTDE,
GELANDEV.VLRMED,
porque eles são acumulados pela função de agregação SUM()

a ordenação abaixo ira gerar o erro de ambiguidade, porque há campos CDC em duas tabelas e o nome já está indicado lá embaixo (I.NOME).
ORDER BY
CDC,
NOME
GOSTEI 1

Mais Respostas

Marcos Batista

Marcos Batista

17/12/2021

você tem o campo
I.UNI_CON
na SELECT, mas não tem na GROUP BY

não faz sentido ter na GROUP BY os campos
GELANSAI.QTDE,
GELANSAI.VLRMED,
GELANDEV.QTDE,
GELANDEV.VLRMED,
porque eles são acumulados pela função de agregação SUM()

a ordenação abaixo ira gerar o erro de ambiguidade, porque há campos CDC em duas tabelas e o nome já está indicado lá embaixo (I.NOME).
ORDER BY
CDC,
NOME




Fiz da maneira que falou mas continua apresentando o mesmo erro, segue a query como ficou (utilizo o firebird)

SELECT
/*SAIDA*/
GELANSAI.ITEM ITEM_SAIDA,
GECADSAI.DATA DATA_SAIDA,
SUM(GELANSAI.QTDE) QTDE_SAIDA,
SUM(GELANSAI.VLRMED) VLRMED_SAIDA,
GECADSAI.CDC CDC_SAIDA,
/*ITENS*/
I.NOME,
I.UNI_CON,
I.CUSTO VLR_UNI,
(SELECT FIRST 1 GEGRUPOS.NOME FROM GEGRUPOS WHERE GEGRUPOS.COD = I.GRU) GRUPO,
(SELECT FIRST 1 NOME FROM TBCENCUS WHERE COD=GECADSAI.CDC) CDC,
/*DEVOLUCAO*/
GELANDEV.ITEM ITEM_DEV,
GECADDEV.DATA DATA_DEV,
SUM(GELANDEV.QTDE) QTDE_DEV,
SUM(GELANDEV.VLRMED) VLRMED_DEV,
GECADDEV.CDC CDC_DEV

FROM GECADSAI
LEFT JOIN GELANSAI ON GECADSAI.ANO=GELANSAI.ANO
AND GECADSAI.MES=GELANSAI.MES
AND GECADSAI.DOC=GELANSAI.DOC
LEFT JOIN GEITENS I ON GELANSAI.ITEM=I.COD
LEFT JOIN TBCENCUS T ON T.COD=GECADSAI.CDC
LEFT JOIN GELANDEV ON GELANDEV.ID_GELANSAI=GELANSAI.ID_GELANSAI
LEFT JOIN GECADDEV ON GECADDEV.ID=GELANDEV.ID_GECADDEV
WHERE GECADSAI.ANO=:ANO
AND GECADSAI.MES=:MES
AND (
(NOT GECADSAI.CDC IS NULL AND GECADSAI.CDC BETWEEN :CDC1 AND :CDC2)
OR
(NOT GECADDEV.CDC IS NULL AND GECADDEV.CDC BETWEEN :CDC1 AND :CDC2)
)
AND (
(NOT GECADSAI.DATA IS NULL AND GECADSAI.DATA BETWEEN :DATA1 AND :DATA2)
OR
(NOT GECADDEV.DATA IS NULL AND GECADDEV.DATA BETWEEN :DATA1 AND :DATA2)
)
AND (COALESCE(GELANSAI.CONSOL,'')='T' OR COALESCE(GECADSAI.CONSOL,'')='T')
GROUP BY GELANSAI.ITEM,
GECADSAI.DATA,
GECADSAI.CDC,
I.NOME,
I.CUSTO,
I.UNI_CON,
GELANDEV.ITEM,
GECADDEV.DATA,
GECADDEV.CDC
ORDER BY GECADSAI.CDC,
I.NOME
GOSTEI 0
Emerson Nascimento

Emerson Nascimento

17/12/2021

SELECT
	/*SAIDA*/
	GELANSAI.ITEM ITEM_SAIDA,
	GECADSAI.DATA DATA_SAIDA,
	SUM(GELANSAI.QTDE) QTDE_SAIDA,
	SUM(GELANSAI.VLRMED) VLRMED_SAIDA,
	GECADSAI.CDC CDC_SAIDA,
	/*ITENS*/
	I.NOME,
	I.UNI_CON,
	I.CUSTO VLR_UNI,
	(SELECT FIRST 1 GEGRUPOS.NOME FROM GEGRUPOS WHERE GEGRUPOS.COD = I.GRU) GRUPO,
	(SELECT FIRST 1 NOME FROM TBCENCUS WHERE COD=GECADSAI.CDC) CDC,
	/*DEVOLUCAO*/
	GELANDEV.ITEM ITEM_DEV,
	GECADDEV.DATA DATA_DEV,
	SUM(GELANDEV.QTDE) QTDE_DEV,
	SUM(GELANDEV.VLRMED) VLRMED_DEV,
	GECADDEV.CDC CDC_DEV
FROM
	GECADSAI
LEFT JOIN
	GELANSAI ON GECADSAI.ANO=GELANSAI.ANO
	AND GECADSAI.MES=GELANSAI.MES
	AND GECADSAI.DOC=GELANSAI.DOC
LEFT JOIN
	GEITENS I ON GELANSAI.ITEM=I.COD
LEFT JOIN
	TBCENCUS T ON T.COD=GECADSAI.CDC
LEFT JOIN
	GELANDEV ON GELANDEV.ID_GELANSAI=GELANSAI.ID_GELANSAI
LEFT JOIN
	GECADDEV ON GECADDEV.ID=GELANDEV.ID_GECADDEV
WHERE
	GECADSAI.ANO=:ANO
	AND GECADSAI.MES=:MES
	AND (
		(NOT GECADSAI.CDC IS NULL AND GECADSAI.CDC BETWEEN :CDC1 AND :CDC2)
		OR
		(NOT GECADDEV.CDC IS NULL AND GECADDEV.CDC BETWEEN :CDC1 AND :CDC2)
	)
	AND (
		(NOT GECADSAI.DATA IS NULL AND GECADSAI.DATA BETWEEN :DATA1 AND :DATA2)
		OR
		(NOT GECADDEV.DATA IS NULL AND GECADDEV.DATA BETWEEN :DATA1 AND :DATA2)
	)
	AND (COALESCE(GELANSAI.CONSOL,'')='T' OR COALESCE(GECADSAI.CONSOL,'')='T')
GROUP BY
	GELANSAI.ITEM,
	GECADSAI.DATA,
	GECADSAI.CDC,
	I.NOME,
	I.CUSTO,
	I.UNI_CON,
	GELANDEV.ITEM,
	GECADDEV.DATA,
	GECADDEV.CDC
ORDER BY
	GECADSAI.CDC,
	I.NOME


para testar, retire as linhas abaixo da instrução.
	(SELECT FIRST 1 GEGRUPOS.NOME FROM GEGRUPOS WHERE GEGRUPOS.COD = I.GRU) GRUPO,
	(SELECT FIRST 1 NOME FROM TBCENCUS WHERE COD=GECADSAI.CDC) CDC,
e uma pergunta: porque esse conteúdo não está num JOIN?

GOSTEI 1
Marcos Batista

Marcos Batista

17/12/2021

SELECT
	/*SAIDA*/
	GELANSAI.ITEM ITEM_SAIDA,
	GECADSAI.DATA DATA_SAIDA,
	SUM(GELANSAI.QTDE) QTDE_SAIDA,
	SUM(GELANSAI.VLRMED) VLRMED_SAIDA,
	GECADSAI.CDC CDC_SAIDA,
	/*ITENS*/
	I.NOME,
	I.UNI_CON,
	I.CUSTO VLR_UNI,
	(SELECT FIRST 1 GEGRUPOS.NOME FROM GEGRUPOS WHERE GEGRUPOS.COD = I.GRU) GRUPO,
	(SELECT FIRST 1 NOME FROM TBCENCUS WHERE COD=GECADSAI.CDC) CDC,
	/*DEVOLUCAO*/
	GELANDEV.ITEM ITEM_DEV,
	GECADDEV.DATA DATA_DEV,
	SUM(GELANDEV.QTDE) QTDE_DEV,
	SUM(GELANDEV.VLRMED) VLRMED_DEV,
	GECADDEV.CDC CDC_DEV
FROM
	GECADSAI
LEFT JOIN
	GELANSAI ON GECADSAI.ANO=GELANSAI.ANO
	AND GECADSAI.MES=GELANSAI.MES
	AND GECADSAI.DOC=GELANSAI.DOC
LEFT JOIN
	GEITENS I ON GELANSAI.ITEM=I.COD
LEFT JOIN
	TBCENCUS T ON T.COD=GECADSAI.CDC
LEFT JOIN
	GELANDEV ON GELANDEV.ID_GELANSAI=GELANSAI.ID_GELANSAI
LEFT JOIN
	GECADDEV ON GECADDEV.ID=GELANDEV.ID_GECADDEV
WHERE
	GECADSAI.ANO=:ANO
	AND GECADSAI.MES=:MES
	AND (
		(NOT GECADSAI.CDC IS NULL AND GECADSAI.CDC BETWEEN :CDC1 AND :CDC2)
		OR
		(NOT GECADDEV.CDC IS NULL AND GECADDEV.CDC BETWEEN :CDC1 AND :CDC2)
	)
	AND (
		(NOT GECADSAI.DATA IS NULL AND GECADSAI.DATA BETWEEN :DATA1 AND :DATA2)
		OR
		(NOT GECADDEV.DATA IS NULL AND GECADDEV.DATA BETWEEN :DATA1 AND :DATA2)
	)
	AND (COALESCE(GELANSAI.CONSOL,'')='T' OR COALESCE(GECADSAI.CONSOL,'')='T')
GROUP BY
	GELANSAI.ITEM,
	GECADSAI.DATA,
	GECADSAI.CDC,
	I.NOME,
	I.CUSTO,
	I.UNI_CON,
	GELANDEV.ITEM,
	GECADDEV.DATA,
	GECADDEV.CDC
ORDER BY
	GECADSAI.CDC,
	I.NOME


para testar, retire as linhas abaixo da instrução.
	(SELECT FIRST 1 GEGRUPOS.NOME FROM GEGRUPOS WHERE GEGRUPOS.COD = I.GRU) GRUPO,
	(SELECT FIRST 1 NOME FROM TBCENCUS WHERE COD=GECADSAI.CDC) CDC,
e uma pergunta: porque esse conteúdo não está num JOIN?




Deu certo meu amigo, muito obrigado!
GOSTEI 0
POSTAR