OQL Group by clause

0
Hi, I'm trying to write a query that summarises a date set by a status and the number of days overdue. The query works fine until I add the following into the group by clause: datediff(DAY,'[%CurrentDateTime%]', MedicareDatabase.MedicareClaim/DVStatusActionRaised) Am I asking to much from OQL or have I got a syntax error? It compiles ok and there are no error in Mendix modeler, just fails with a runtime error. If I take the group by out and remove count runs fine.. Full query is below: Select MedicareDatabase.DVStatus/DVStatus as DVStatus, datediff(DAY,'[%CurrentDateTime%]', MedicareDatabase.MedicareClaim/DVStatusActionRaised)as DVStatusAge, count(*) as numberClaims from MedicareDatabase.MedicareClaim inner join MedicareDatabase.MedicareClaim/MedicareDatabase.MedicareClaim_DVStatus/MedicareDatabase.DVStatus where MedicareDatabase.DVStatus/DVStatus = 'Warning' and MedicareDatabase.MedicareClaim.DVStatusActionComplete is null and MedicareDatabase.MedicareClaim.DVStatusActionRaised is not null group by MedicareDatabase.DVStatus/DVStatus, datediff(DAY,'[%CurrentDateTime%]', MedicareDatabase.MedicareClaim/DVStatusActionRaised)
asked
1 answers
0

I don't know if you are asking too much of OQL, but you could group by DATEPART(YEAR,MedicareDatabase.MedicareClaim/DVStatusActionRaised),DATEPART(MONTH,MedicareDatabase.MedicareClaim/DVStatusActionRaised),DATEPART(DAY,MedicareDatabase.MedicareClaim/DVStatusActionRaised) and see if you can calculate the diff in the select clause

answered