Group By - Apresentando mensagem de erro
17/12/2021
0
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
Post mais votado
17/12/2021
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
Emerson Nascimento
Mais Posts
17/12/2021
Marcos Batista
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
17/12/2021
Emerson Nascimento
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,
17/12/2021
Marcos Batista
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!
Clique aqui para fazer login e interagir na Comunidade :)