[firebase-br] Union com Group by
Tallys Ferrante
tallysferrante em gmail.com
Sex Jan 17 09:06:06 -03 2014
Bom dia Pessoal, estou quebrando a cabeça com esse código, vi diversos
exemplos na net e não sei mais como resolver, me retorna o seguinte erro:
Invalid token.
Dynamic SQL Error.
SQL error code = -104.
Invalid expression in the select list (not contained in either an aggregate
function or the GROUP BY clause).
SELECT ICMS,
CODIGO_CFOP,
DATA_ENTRADA,
ALIQUOTA_ICMS,
SUM(TOTAL_OPERACAO),
SUM(BASE_CALCULO_ICMS),
SUM(VALOR_ICMS),
SUM(BASE_CALCULO_ICMS_ST),
SUM(VALOR_ICMS_ST),
SUM(VALOR_IPI),
SUM(BASE_CALCULO_PIS),
SUM(VALOR_PIS),
SUM(BASE_CALCULO_COFINS),
SUM(VALOR_COFINS)
FROM(
SELECT I.CST_ICMS_ORIGEM || I.CST_CSOSN_ICMS AS ICMS,
I.CODIGO_CFOP,
E.DATA_ENTRADA,
NVL(I.ALIQUOTA_ICMS) AS ALIQUOTA_ICMS,
CAST(NVL(I.VALOR_TOTAL) AS NUMERIC(15,2)) AS
TOTAL_OPERACAO,
CAST(NVL(I.BASE_CALCULO_ICMS) AS NUMERIC(15,2)) AS
BASE_CALCULO_ICMS,
CAST(NVL(I.VALOR_ICMS) AS NUMERIC(15,2)) AS
VALOR_ICMS,
CAST(NVL(I.BASE_CALCULO_ICMS_ST) AS NUMERIC(15,2)) AS
BASE_CALCULO_ICMS_ST,
CAST(NVL(I.VALOR_ICMS_ST) AS NUMERIC(15,2)) AS
VALOR_ICMS_ST,
CAST(NVL(I.VALOR_IPI) AS NUMERIC(15,2)) AS VALOR_IPI,
CAST(NVL(I.BASE_CALCULO_PIS) AS NUMERIC(15,2)) AS
BASE_CALCULO_PIS,
CAST(NVL(I.VALOR_PIS) AS NUMERIC(15,2)) AS VALOR_PIS,
CAST(NVL(I.BASE_CALCULO_COFINS) AS NUMERIC(15,2)) AS
BASE_CALCULO_COFINS,
CAST(NVL(I.VALOR_COFINS) AS NUMERIC(15,2)) AS
VALOR_COFINS
FROM ENTRADA_NOTA_FISCAL_ITEM I
JOIN ENTRADA_NOTA_FISCAL E ON
E.ID_ENTRADA_NOTA_FISCAL = I.ID_ENTRADA_NOTA_FISCAL
WHERE I.CODIGO_CFOP IS NOT NULL
UNION ALL
SELECT E.CST_ICMS_ORIGEM || E.CST_ICMS AS ICMS,
E.CODIGO_CFOP,
E.DATA_ENTRADA,
NVL(E.ALIQUOTA_ICMS) AS ALIQUOTA_ICMS,
CAST(NVL(E.VALOR_NOTA_FISCAL) AS NUMERIC(15,2)) AS
TOTAL_OPERACAO,
CAST(NVL(E.BASE_CALCULO_ICMS) AS NUMERIC(15,2)) AS
BASE_CALCULO_ICMS,
CAST(NVL(E.VALOR_ICMS) AS NUMERIC(15,2)) AS
VALOR_ICMS,
CAST(NVL(E.BASE_CALCULO_ICMS_ST) AS NUMERIC(15,2)) AS
BASE_CALCULO_ICMS_ST,
CAST(NVL(E.VALOR_ICMS_ST) AS NUMERIC(15,2)) AS
VALOR_ICMS_ST,
CAST(NVL(E.VALOR_IPI) AS NUMERIC(15,2)) AS VALOR_IPI,
CAST(NVL(E.BASE_CALCULO_PIS) AS NUMERIC(15,2)) AS
BASE_CALCULO_PIS,
CAST(NVL(E.VALOR_PIS) AS NUMERIC(15,2)) AS VALOR_PIS,
CAST(NVL(E.BASE_CALCULO_COFINS) AS NUMERIC(15,2)) AS
BASE_CALCULO_COFINS,
CAST(NVL(E.VALOR_COFINS) AS NUMERIC(15,2)) AS
VALOR_COFINS
FROM ENTRADA_NOTA_FISCAL E
WHERE E.CODIGO_MODELO <> '')
WHERE DATA_ENTRADA BETWEEN :INICIO AND :FINAL
GROUP BY 1,2,4
ORDER BY 1,2
Mais detalhes sobre a lista de discussão lista