Geek asks and answers

Oracle & # 8594; NOT THE EXPRESSION SELECTION GROUP

Below is my request. I am not sure that the cause of the group error is not an expression error. Any help is much appreciated. =)

SELECT 
    DTA_PRODUCAO AS AF002_DTA_PRODUCAO
,   COD_MATERIAL AS AD001_COD_MATERIAL 
,   COD_FILIAL AS AD004_COD_FILIAL
,   NVL(SUM(DQTD_CONSUMOT),0)  AS AF002_DQTD_CONSUMOT
,  (SELECT NVL(SUM(DQTD_CONSUMOT),0) FROM MYDB.DSO_TERMICO M WHERE M.COD_FILIAL = D.COD_FILIAL AND M.CODPRO = D.COD_MATERIAL   AND M.ZTL_DATA >= SUBSTR(D.DTA_PRODUCAO,0,6)||'01' AND M.ZTL_DATA <= D.DTA_PRODUCAO) AS AF002_MQTD_CONSUMOT
,  (SELECT NVL(SUM(DQTD_CONSUMOT),0) FROM MYDB.DSO_TERMICO M WHERE M.COD_FILIAL = D.COD_FILIAL AND M.CODPRO = D.COD_MATERIAL   AND M.ZTL_DATA >= SUBSTR(D.DTA_PRODUCAO,0,4)||'0101' AND M.ZTL_DATA <= D.DTA_PRODUCAO) AS AF002_AQTD_CONSUMOT
FROM
    (SELECT 
       DTA_PRODUCAO
       ,CODPRO AS COD_MATERIAL 
       ,COD_FILIAL AS COD_FILIAL 
      , CASE WHEN DAUX.ZTL_DATA = TMP.DTA_PRODUCAO THEN DAUX.DIACONSUMO ELSE 0 END AS DQTD_CONSUMOT
      FROM MYDB.DSO_TERMICO DAUX
      INNER JOIN (SELECT DISTINCT MYDB.DSO_TERMICO.ZTL_DATA AS DTA_PRODUCAO FROM MYDB.DSO_TERMICO) TMP ON SUBSTR(TMP.DTA_PRODUCAO,1,4)=SUBSTR(DAUX.ZTL_DATA,1,4))
  D
  GROUP BY 
  DTA_PRODUCAO,
  COD_MATERIAL,
  COD_FILIAL
  ORDER BY 2,1;
+3
source share
1 answer

I think I understand what is happening. Here is your subquery:

 (SELECT NVL(SUM(DQTD_CONSUMOT),0)
  FROM MYDB.DSO_TERMICO M
  WHERE M.COD_FILIAL = D.COD_FILIAL AND
        M.CODPRO = D.COD_MATERIAL   AND
        M.ZTL_DATA >= SUBSTR(D.DTA_PRODUCAO,0,6)||'01' AND 
        M.ZTL_DATA <= D.DTA_PRODUCAO
) AS AF002_MQTD_CONSUMOT

Everything looks kosher. The three fields mentioned in whereare in the sentence group by. The expression DQTD_CONSUMOT(which is important D.DQTD_CONSUMOT) is not, but it is in sum(). What could be the problem?

, . sum() . : " D.DQTD_CONSUMOT, group by. !".

, "":

 (SELECT count(*)
  FROM MYDB.DSO_TERMICO M
  WHERE M.COD_FILIAL = D.COD_FILIAL AND
        M.CODPRO = D.COD_MATERIAL   AND
        M.ZTL_DATA >= SUBSTR(D.DTA_PRODUCAO,0,6)||'01' AND 
        M.ZTL_DATA <= D.DTA_PRODUCAO
 ) *  NVL(SUM(DQTD_CONSUMOT),0) AS AF002_MQTD_CONSUMOT

, , . - :

sum(DQTD_CONSUMOT) over (partition by D.COD_FILIAL, D.COD_MATERIAL, SUBSTR(D.DTA_PRODUCAO,0,6))

- . , .

+3

More articles:


All Articles