Error group by Oracle

Asked

Viewed 379 times

2

When I try to make this group by in oracle it returns me message saying that this is not a group by expression

SELECT produto.cd_produto, produto.ds_produto, est_pro.cd_estoque,
      estoque.ds_estoque, fornecedor.cd_fornecedor,
      fornecedor.nm_fornecedor, prod_atend.cd_lote,
      prod_atend.dt_validade, est_pro.qt_estoque_atual,
      itent_pro.QT_ATENDIDA, ent_pro.nr_documento
 FROM produto, estoque, fornecedor, est_pro, prod_atend, ent_pro, itent_pro
WHERE produto.cd_produto = est_pro.cd_produto
  AND fornecedor.cd_fornecedor = produto.cd_ultimo_fornecedor
  AND prod_atend.cd_produto = produto.cd_produto
  AND estoque.cd_estoque = est_pro.cd_estoque
  AND ITENT_PRO.CD_ENT_PRO = ENT_PRO.CD_ENT_PRO
  AND ITENT_PRO.CD_PRODUTO = PRODUTO.CD_PRODUTO
  AND est_pro.cd_estoque = '15'
  group by produto.CD_PRODUTO, produto.DS_PRODUTO;
  • To make it easier for other people to read your query, keep the same upper case formatting every time you reference a field. Still, not respecting uppercase/lowercase as they are in the field names, can make the query incompatible with other servers, normally undesirable.

2 answers

1

Of the documentation in https://docs.oracle.com/javadb/10.6.1.0/ref/rrefsqlj32654.html:

Selectitems in the Selectexpression with a GROUP BY clause must contain only Aggregates or grouping Columns.

That is, ALL columns in your select must be part of GROUP BY or have an aggregation function such as AVG() or FIRST().

This is because the bank engine does not know that there is only one est_pro.cd_estoque for each group he does, and if there is more than one what he does is not defined. Then one can use, for example SUM(est_pro.cd_estoque). This goes for all columns you want to select.

  • I saw no reason for "group by".

1

Peter by what I saw in your select you are trying to group only by code and product description. However in the group by function you need to put all the fields that are in select

Browser other questions tagged

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