SQL VBA Group by error with TRUNC() function

Question

im doing a query on vba excel, i ave a problem with this query:

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

I get this error: ORA-00979: not a GROUP BY expression this error there is only when i do the TRUNC() function, someone can help me and explain this group by

UPDATE I tryed to use oracle and i hav e edit the query like this:

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)

but i have always this error: ORA-00979: not a GROUP BY expression

Why the trunc function give me this error?

You typically GROUP BY the same columns as you SELECT, except those who are arguments to set functions. — jarlh
– jarlh, Commented Nov 16, 2020 at 7:57
DISTINCT is not a function, it's a part of SELECT DISTINCT, and works on the whole selected rows. — jarlh
– jarlh, Commented Nov 16, 2020 at 7:58
GROUP BY eliminates duplicate rows, so no need to do SELECT DISTINCT. — jarlh
– jarlh, Commented Nov 16, 2020 at 7:59
i tryed to dont use the distinct, but i have the same error, if i dont put the TRUNC() function its work, so why? — bolzy9
– bolzy9, Commented Nov 16, 2020 at 8:25
You should create your SQL statement first with SQL developer or something similar. As long as it's not working there, it can't work from VBA either. Only if it works there, put it into VBA. ♦ Use ADODB.Parameter to pass the date criteria to Oracle, see stackoverflow.com/a/60640185/7599798 ♦ To check if a date is Sunday you should use the Oracle Weekday function — FunThomas
– FunThomas, Commented Nov 16, 2020 at 9:23

NickW · Accepted Answer · 2020-11-16 10:30:40Z

The GROUP BY clause needs to include all the fields in your SELECT that are not being GROUPED. You have omitted this one:

(TRUNC(MAX(t1.DATA_PREV_FIN_LAV)) - TRUNC(t4.DATA_INIZIO)) AS DIFF

Even though you have an aggregation function in there (MAX), overall this is a TRUNC statement and so is not an aggregation.

I think logically the following 2 statements are the same (at least as long as B < A):

MAX(A) - B == MAX(A-B)

therefore you should be able to re-write your statement as the following and still get the same result:

MAX(TRUNC(t1.DATA_PREV_FIN_LAV) - TRUNC(t4.DATA_INIZIO)) AS DIFF

Gordon Linoff · Accepted Answer · 2020-11-16 12:18:49Z

You are missing the column TRUNC(t4.DATA_INIZIO) in the 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);

Note that Oracle supports date literals using the DATE keyword. I strongly recommend that you use that.

Collectives™ on Stack Overflow

SQL VBA Group by error with TRUNC() function

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related