时间戳

首页 > 解决方案 > SQL VBA 使用 TRUNC() 函数按错误分组

sql - SQL VBA 使用 TRUNC() 函数按错误分组

问题描述

我正在对 vba excel 进行查询，我对此查询有疑问：

SQLStr = "SELECT DISTINCT (t2.COD_CF) AS CODICE_CLIENTE, (t2.RAG_SOC_CF) AS CLIENTE, (t1.DES_HEAD_DOC) AS COMMESSA_E_DESCRIZIONE, t1.DATA_PREV_FIN_LAV AS GREZZO_CONFERMATO,  MIN(t4.DATA_INIZIO) AS PRIMO_CARICO, MAX(t4.DATA_FINE) AS ULTIMO_CARICO, " & _
"(TRUNC(MAX(t1.DATA_PREV_FIN_LAV)) - TRUNC(t4.DATA_INIZIO) ) - " & _
"((((TRUNC(MAX(t1.DATA_PREV_FIN_LAV),'D'))-(TRUNC(t4.DATA_INIZIO,'D')))/7)*2) - " & _
"(CASE WHEN TO_CHAR(t4.DATA_INIZIO,'DY','nls_date_language=english')='SUN' THEN 1 ELSE 0 END) - " & _
"(CASE WHEN TO_CHAR(MAX(t1.DATA_PREV_FIN_LAV),'DY','nls_date_language=english')='SAT' THEN 1 ELSE 0 END) as GG_DIFFERENZA " & _
"FROM COMM_LAV t1 " & _
"INNER JOIN CF t2 ON t1.COD_CF_INTE = t2.COD_CF " & _
"INNER JOIN COMM_LAV_LNK t3 ON t1.DOC_ID = t3.DOC_ID " & _
"INNER JOIN ORP_EFF_CICLI_ESEC t4 ON t3.LNK_DOC_ID = t4.DOC_ID " & _
"WHERE t1.DATA_PREV_FIN_LAV >= '" & startDate & "' AND t1.DATA_PREV_FIN_LAV <= '" & endDate & "' AND t4.COD_CICLO <> 'LEV01' " & _
"GROUP BY t2.COD_CF, t2.RAG_SOC_CF, t1.DES_HEAD_DOC, t1.DATA_PREV_FIN_LAV " & _
"ORDER BY t1.DES_HEAD_DOC, t1.DATA_PREV_FIN_LAV, MIN(t4.DATA_INIZIO) "

我收到此错误：ORA-00979: not a GROUP BY expression 此错误仅在我执行 TRUNC() 函数时出现，有人可以帮助我并通过以下方式解释该组

更新 我尝试使用 oracle 并且我已经编辑了这样的查询：

SELECT (t2.COD_CF) AS CODICE_CLIENTE, (t2.RAG_SOC_CF) AS CLIENTE, (t1.DES_HEAD_DOC) AS COMMESSA_E_DESCRIZIONE, t1.DATA_PREV_FIN_LAV AS GREZZO_CONFERMATO,  MIN(t4.DATA_INIZIO) AS PRIMO_CARICO, MAX(t4.DATA_FINE) AS ULTIMO_CARICO,  
(TRUNC(MAX(t1.DATA_PREV_FIN_LAV)) - TRUNC(t4.DATA_INIZIO)) AS DIFF
FROM COMM_LAV t1
INNER JOIN CF t2 ON t1.COD_CF_INTE = t2.COD_CF 
INNER JOIN COMM_LAV_LNK t3 ON t1.DOC_ID = t3.DOC_ID 
INNER JOIN ORP_EFF_CICLI_ESEC t4 ON t3.LNK_DOC_ID = t4.DOC_ID 
WHERE t1.DATA_PREV_FIN_LAV >= '01-OCT-20' AND t1.DATA_PREV_FIN_LAV <= '30-OCT-20' AND t4.COD_CICLO <> 'LEV01' 
GROUP BY t2.COD_CF, t2.RAG_SOC_CF, t1.DES_HEAD_DOC, t1.DATA_PREV_FIN_LAV
ORDER BY t1.DES_HEAD_DOC, t1.DATA_PREV_FIN_LAV, MIN(t4.DATA_INIZIO)

但我总是出现这个错误：ORA-00979: not a GROUP BY expression

为什么 trunc 函数给我这个错误？

标签： sqlvbaoraclegroup-by

解决方案

您缺少TRUNC(t4.DATA_INIZIO)以下列GROUP BY：

SELECT t2.COD_CF AS CODICE_CLIENTE, t2.RAG_SOC_CF AS CLIENTE,
       t1.DES_HEAD_DOC AS COMMESSA_E_DESCRIZIONE, 
       t1.DATA_PREV_FIN_LAV AS GREZZO_CONFERMATO,
       MIN(t4.DATA_INIZIO) AS PRIMO_CARICO,
       MAX(t4.DATA_FINE) AS ULTIMO_CARICO,  
       (TRUNC(MAX(t1.DATA_PREV_FIN_LAV)) - 
        TRUNC(t4.DATA_INIZIO)
--------^ Not in GROUP BY
       ) AS DIFF
FROM COMM_LAV t1 JOIN
     CF t2 
     ON t1.COD_CF_INTE = t2.COD_CF JOIN
     COMM_LAV_LNK t3
     ON t1.DOC_ID = t3.DOC_ID JOIN
     ORP_EFF_CICLI_ESEC t4
     ON t3.LNK_DOC_ID = t4.DOC_ID JOIN
WHERE t1.DATA_PREV_FIN_LAV >= DATE '2020-10-01' AND 
      t1.DATA_PREV_FIN_LAV <= DATE '2020-10-30' AND
      t4.COD_CICLO <> 'LEV01' 
GROUP BY t2.COD_CF, t2.RAG_SOC_CF, t1.DES_HEAD_DOC, t1.DATA_PREV_FIN_LAV,
         TRUNC(t4.DATA_INIZIO)
---------^ add to GROUP BY
ORDER BY t1.DES_HEAD_DOC, t1.DATA_PREV_FIN_LAV, MIN(t4.DATA_INIZIO);

请注意，Oracle 支持使用DATE关键字的日期文字。我强烈建议您使用它。

推荐阅读