Erro em select com procedure

2 respostas
firebird
D

Bom dia, pessoal.
Estou efetuando o select abaixo:

SELECT
  F.NOPE AS CFOP, 
  F.NFISCAL AS NDOC, 
  F.DATA AS DEMI, 
  F.PERC_ICMS, 
  F.PERC_IPI, 
  F.PERC_PIS, 
  F.PERC_COFINS, 
  sum(
    CASE
      WHEN F.TNOTA = 'E' THEN abs(F.TOTAL_VENDIDO + coalesce(F.VR_IPI, 0))
      ELSE (F.TOTAL_VENDIDO + coalesce(F.VR_IPI, 0))
    END
  ) AS VR_CONTABIL,
  sum(F.VR_BC_ICMS) AS VR_BCICMS, 
  sum(
    CASE
      WHEN F.TNOTA = 'E' THEN F.VR_ICMS
      ELSE (F.VR_ICMS)
    END
  ) AS VR_ICMS,
  sum(F.VR_BC_IPI) AS VR_BC_IPI, 
  sum(F.VR_IPI) AS VR_IPI, 
  sum(
    CASE
      WHEN F.TP_SIT_TRIB_IPI = '9' THEN 0.00
      ELSE 
        CASE
          WHEN F.TNOTA = 'E' THEN abs(F.VR_IPI)
          ELSE (F.VR_IPI)
        END
    END
  ) AS VR_IPI,
  sum(
    CASE
      WHEN F.TP_SIT_TRIB_IPI = '9' THEN 0.00
      ELSE 
        CASE
          WHEN F.TNOTA = 'E' THEN abs(F.VR_PIS)
          ELSE (F.VR_PIS)
        END
    END
  ) AS VR_PIS,
  sum(
    CASE
      WHEN F.TP_SIT_TRIB_IPI = '9' THEN 0.00
      ELSE 
        CASE
          WHEN F.TNOTA = 'E' THEN abs(F.VR_COFINS)
          ELSE (F.VR_COFINS)
        END
    END
  ) AS VR_COFINS,
  '1' AS CODIMPICMS, 
  '2' AS CODIMPIPI, 
  '3' AS CODIMPPIS, 
  '4' AS CODIMPCOFINS, 
  '2' AS CODTIPOIMPFISCAL,
  CASE
    WHEN F.PERC_ICMS = 4 THEN '4%'
    WHEN F.PERC_ICMS = 7 THEN '7%'
    WHEN F.PERC_ICMS = 12 THEN '12%'
    WHEN F.PERC_ICMS = 13.3 THEN '13,3%'
    WHEN F.PERC_ICMS = 18 THEN '18%'
    ELSE 'Outras'
  END AL_ICMS,
  decode(F.PERC_IPI, NULL, 'Outras', F.PERC_IPI || '%') AS AL_IPI,
  CASE
    WHEN F.PERC_PIS = 1.65 THEN '1,65%'
    WHEN F.PERC_PIS = 0.65 THEN '0,65%'
    ELSE 'Outras'
  END AL_PIS,
  CASE
    WHEN F.PERC_COFINS = 7.60 THEN '7,60%'
    WHEN F.PERC_COFINS = 3 THEN '3%'
    ELSE 'Outras'
  END AL_COFINS,
  F.COD_EMPRESA, 
  F.PRODUTO
FROM 
  SP_VENDA_ITENS('01.01.1900', '31.12.2999', 'E', NULL) F
WHERE
  F.ORIGEM = 'Itens-NF' 
  AND F.NOPE > 0 
  AND F.INTESCR = 'S'
GROUP BY 
  1, 2, 3, 4, 5, 6, 7, 21, 22, 23, 24
ORDER BY 
  1, 2

Mas estou recebendo o erro:

Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause).

A ordenação entre where, group by e order by está correta, alguém sabe o que pode ser?

2 Respostas

pmlm

As colunas F.COD_EMPRESA, F.PRODUTO não estão incluídas no teu group by

D

@pmlm Deu certo, a view completa ficou assim:

create or alter view BI_FISCAL (
PRODUTO,
QTDE,
VUNIT,
VTOTAL,
VCUSTO,
VICM,
AL_ICMS,
VIPI,
AL_IPI,
VR_PIS,
AL_PIS,
VR_COFINS,
AL_COFINS,
CODIMPICMS,
CODIMPIPI,
CODIMPPIS,
CODIMPCOFINS,
CODTIPOIMPFISCAL,
DCHEG,
CFOP,
COD_EMPRESA,
NOTA,
TP_IMPOSTO,
TP_SE)
as

– Compras
select F.PRODUTO, F.QTDE, F.VUNIT, F.VTOTAL, F.VR_CUSTO as VCUSTO, sum(
case F.TP_SIT_TRIB
when ‘3’ then 0.00
else F.VR_ICMS
end) as VICM,
case
when F.PERC_ICMS = 4 then ‘4%’
when F.PERC_ICMS = 7 then ‘7%’
when F.PERC_ICMS = 12 then ‘12%’
when F.PERC_ICMS = 13.3 then ‘13,3%’
when F.PERC_ICMS = 18 then ‘18%’
else ‘Outras’
end AL_ICMS,
sum(
case
when F.TP_SIT_TRIB_IPI = ‘9’ then 0.00
else F.VR_IPI
end) as VIPI,
decode(F.PERC_IPI, null, ‘Outras’, F.PERC_IPI || ‘%’) as AL_IPI, sum(F.VR_PIS) as VR_PIS,
case
when F.PERC_PIS = 1.65 then ‘1,65%’
when F.PERC_PIS = 0.65 then ‘0,65%’
else ‘Outras’
end AL_PIS,
sum(F.VR_COFINS) as VR_COFINS,
case
when F.PERC_COFINS = 7.60 then ‘7,60%’
when F.PERC_COFINS = 3 then ‘3%’
else ‘Outras’
end AL_COFINS,
‘1’ as CODIMPICMS, ‘2’ as CODIMPIPI, ‘3’ as CODIMPPIS, ‘4’ as CODIMPCOFINS, ‘1’ as CODTIPOIMPFISCAL,
F.DATA as DCHEG, F.NOPE as CFOP, F.COD_EMPRESA, F.NDOC as NOTA, ‘CREDITO (ENTRADAS)’ as TP_IMPOSTO,
‘E’ as TP_SE
from SP_COMPRA_ITENS(‘01.01.1900’, ‘31.12.2999’) F
where F.ORIGEM = ‘Itens-NF’ and
F.NOPE > 0 and
F.INTESCR = ‘S’
group by 1, 2, 3, 4, 5, 7, 9, 11, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24

union all

 Vendas

select F.PRODUTO, F.QTDE, F.VUNIT, F.VTOTAL, F.VR_CUSTO as VCUSTO, sum(

case

when F.TNOTA = E then F.VR_ICMS

else (F.VR_ICMS)

end) as VICM,

case

when F.PERC_ICMS = 4 then 4%’

when F.PERC_ICMS = 7 then 7%’

when F.PERC_ICMS = 12 then 12%’

when F.PERC_ICMS = 13.3 then 13,3%’

when F.PERC_ICMS = 18 then 18%’

else Outras

end AL_ICMS,

sum(

case

when F.TP_SIT_TRIB_IPI = 9 then 0.00

else case

when F.TNOTA = E then abs(F.VR_IPI)

else (F.VR_IPI)

end

end) as VIPI,

decode(F.PERC_IPI, null, Outras, F.PERC_IPI || ‘%’) as AL_IPI, sum(F.VR_PIS) as VR_PIS,

case

when F.PERC_PIS = 1.65 then 1,65%’

when F.PERC_PIS = 0.65 then 0,65%’

else Outras

end AL_PIS,

sum(F.VR_COFINS) as VR_COFINS,

case

when F.PERC_COFINS = 7.60 then 7,60%’

when F.PERC_COFINS = 3 then 3%’

else Outras

end AL_COFINS,

1 as CODIMPICMS, 2 as CODIMPIPI, 3 as CODIMPPIS, 4 as CODIMPCOFINS, 2 as CODTIPOIMPFISCAL,

F.DATA as DCHEG, F.NOPE as CFOP, F.COD_EMPRESA, F.NFISCAL as NOTA, DEBITO (SAIDAS) as TP_IMPOSTO,

S as TP_SE

from SP_VENDA_ITENS(01.01.1900, 31.12.2999, E, null) F

inner join SERIE_NF S on F.SEQSERIE = S.SEQSERIE

inner join MODELO_NF MN on MN.SEQMODELO = S.SEQMODELO

where F.ORIGEM = Itens-NF and

F.NOPE > 0 and

F.INTESCR = S and

MN.MODELO = 55 and

S.SERIE not like ‘%SERV%’

group by 1, 2, 3, 4, 5, 7, 9, 11, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24;
Criado 29 de novembro de 2021
Ultima resposta 29 de nov. de 2021
Respostas 2
Participantes 2