Itgensql056 DATEPART and GROUP BY

Questions
, ,
1

We have a problem with the following statement:

select DATEPART("week", dateWorked) as kw
,      sum(hoursworked) as abrechenbar
from   Autotask.TimeEntries.TimeEntries 
where  (dateWorked between '01-01-2023' and '12-31-2023') 
and    isnonbillable = 0
group 
by     DATEPART("week", dateWorked);

Error-Message:

itgensql056:
Syntax error between the two '’ on line 3, column 17:
select DATEPART(“week”…e = 0group by DATEPART(***“week”, dateWorked)order by DATE…
Error: mismatched input ‘(’ expecting {, ‘;’}.

Version: 23.0.114-INTERNAL+4399

2

The itgensql056 error occurs since Invantive UniversalSQL (sadly) does not allow SQL-functions to be used with group operators in one step.

The typical solution is to use an inline view, leading to:

select kw
,      sum(abrechenbar) as abrechenbar
from   ( select DATEPART("week", dateWorked) kw
         ,      hoursworked abrechenbar
         from   Autotask.TimeEntries.TimeEntries 
         where  (dateWorked between to_date('20230101', 'YYYYMMDD') and to_date('20231231', 'YYYYMMDD')) 
         and    isnonbillable = 0
       )
group 
by     kw

In this query also the date format has been made fixed to avoid relying on implicit data type conversions.

3

Thanks very much, it works perfect.

1 like
4

Dit topic is 7 dagen na het laatste antwoord automatisch gesloten. Nieuwe antwoorden zijn niet meer toegestaan.