This happens, because you used fields in the query that need to be in group by
, in case the field PRODUTO
table ITMVTO_ESTOQUE
:
WHERE U.CD_PRODUTO = IE.PRODUTO
To fix this, just add the field PRODUTO
in his group by
after the where
of query:
SELECT SUM(QT_MOVIMENTACAO)/(SELECT U.VL_FATOR FROM UNI_PRO U WHERE U.CD_PRODUTO = IE.PRODUTO AND
TP_RELATORIO = 'C' )
FROM ITMVTO_ESTOQUE IE
WHERE IE.DH_MVTO_ESTOQUE BETWEEN TO_DATE(TO_CHAR(SYSDATE, 'dd/mm/yyyy'),'dd/mm/yyyy')-31 AND
TO_DATE(TO_CHAR(SYSDATE, 'dd/mm/yyyy'),'dd/mm/yyyy')-1
GROUP BY IE.PRODUTO;
But this implies that your query will return one line per product.
If you only want a line containing the totalizer, it is possible to make your query a sub-query:
SELECT SUM(TOTAL_MOVIMENTO) TOT
FROM (
SELECT SUM(QT_MOVIMENTACAO)/(SELECT U.VL_FATOR FROM UNI_PRO U WHERE U.CD_PRODUTO = IE.PRODUTO AND
TP_RELATORIO = 'C' ) TOTAL_MOVIMENTO
FROM ITMVTO_ESTOQUE IE
WHERE IE.DH_MVTO_ESTOQUE BETWEEN TO_DATE(TO_CHAR(SYSDATE, 'dd/mm/yyyy'),'dd/mm/yyyy')-31 AND
TO_DATE(TO_CHAR(SYSDATE, 'dd/mm/yyyy'),'dd/mm/yyyy')-1
GROUP BY IE.PRODUTO
);
With this you will only have a totalizing line with the column called TOT
.
See an online example: http://sqlfiddle.com/#! 4/098ead/11
cannot do a calculation like this, of a field (qt_move) with a select inside another select. You need to put the other table (UNI_PRO) in the
FROM
for ai yes use (can make a Join, cross apply, etc)– Ricardo Pontual