Operation using another table field

Asked

Viewed 24 times

0

I need to calculate the amount of consumption of a material. And I wanted to look for the multiplier factor of the unit of another table, but the system presents me the following message:

"ORA-00937: not a simple group function"

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 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
sql oracle

  • 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)

2 answers

0

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

0

The error occurs because in your subselect you are making a filter for the main table product. And since you did not indicate that the query should be grouped by code, you end up having this problem even.

To solve would have to include the column IE.PRODUTO in the group by.

Example:

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 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

Another thing that is possible to do, is to bring the total using SUM OVER, thus it is not necessary to carry out the grouping.

Behold:

SELECT SUM(QT_MOVIMENTACAO) OVER(ORDER BY IE.PRODUTO) /(SELECT U.VL_FATOR FROM UNI_PRO U WHERE U.CD_PRODUTO = IE.PRODUTO AND TP_RELATORIO = 'C' )
  FROM ITMVTO_ESTOQUE IE
 WHERE 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
 ORDER BY IE.PRODUTO

Browser other questions tagged sql oracle

You are not signed in. Login or sign up in order to post.