Group By - Apresentando mensagem de erro
/*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
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
Curtidas 0
Melhor post
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
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
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
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
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,
GOSTEI 1
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,
Deu certo meu amigo, muito obrigado!
GOSTEI 0