#1  
Old 20th November 2018, 11:37 AM
AJM AJM is offline
Junior Member
 
Join Date: Nov 2018
Posts: 2
Default Grouping on a function calculated field

Hi All,

Is it possible to group a NexusDB query on a field calculated by a function?
I have a function to add a specified number of days to a date field (DATEADD function from NexusDB site if anyone wants to look it up) but I then have a case where I need to group by the calculated field.

Normally if it was just a calc field you would add the calc into the group by clause and the query is happy, but that doesn't seem the case for functions.

In SQL I could use a CTE, but not sure how to go about it under NexusDB.

The scenario is basically :
SELECT DateFieldA, DATEADD(DateFieldB, aysParam) as CalcDate, SUM(SomeField) as TargetValue
FROM TableA
GROUP BY DateFieldA, CalcDate

The query will not run using either CalcDate or DATEADD in the group clause.
Any way to get a result set out for this scenario at all?

Would like to avoid pulling it into a memory table to work with as it could involve a few 100K records.

Cheers
Andrew
  #2  
Old 22nd November 2018, 06:05 PM
Eivind Eivind is offline
Administrator
 
Join Date: Mar 2008
Posts: 161
Default

Quote:
Originally Posted by AJM View Post
Hi All,

Is it possible to group a NexusDB query on a field calculated by a function?
I have a function to add a specified number of days to a date field (DATEADD function from NexusDB site if anyone wants to look it up) but I then have a case where I need to group by the calculated field.

Normally if it was just a calc field you would add the calc into the group by clause and the query is happy, but that doesn't seem the case for functions.

In SQL I could use a CTE, but not sure how to go about it under NexusDB.

The scenario is basically :
SELECT DateFieldA, DATEADD(DateFieldB, aysParam) as CalcDate, SUM(SomeField) as TargetValue
FROM TableA
GROUP BY DateFieldA, CalcDate

The query will not run using either CalcDate or DATEADD in the group clause.
Any way to get a result set out for this scenario at all?

Would like to avoid pulling it into a memory table to work with as it could involve a few 100K records.

Cheers
Andrew
Grouping on a function result is not supported afaik. You can use a subquery like this, and avoid using the dateadd function (example uses Northwind db):

select orderdate, neworderdate, count(*) from
(select orderdate + interval :i day as neworderdate, * from orders) O
group by orderdate, neworderdate
  #3  
Old 18th January 2019, 04:26 PM
AJM AJM is offline
Junior Member
 
Join Date: Nov 2018
Posts: 2
Default

Thanks Eivind, that looks like it will do nicely to get me out of trouble.

Just in regards to the interval command, is it usable in a where clause with the Param as the Interval value?

Have been trying using a clause of 'where ReceivalDate + interval :ExtraDays day < :SomeDate' with an ExtraDays value of 30 and it seems to evaluate differently than if I use 'where ReceivalDate + interval '30' day < :SomeDate'


Cheers
Andrew
  #4  
Old 24th January 2019, 12:03 AM
Eivind Eivind is offline
Administrator
 
Join Date: Mar 2008
Posts: 161
Default

Quote:
Originally Posted by AJM View Post
Thanks Eivind, that looks like it will do nicely to get me out of trouble.

Just in regards to the interval command, is it usable in a where clause with the Param as the Interval value?

Have been trying using a clause of 'where ReceivalDate + interval :ExtraDays day < :SomeDate' with an ExtraDays value of 30 and it seems to evaluate differently than if I use 'where ReceivalDate + interval '30' day < :SomeDate'


Cheers
Andrew
If it gives no errors, the same results should be expected. Please provide us with a reproducible case, and check that the difference isn't just same rows but differently sorted (assuming you don't have an ORDER BY clause).


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
What triggers a calculated field? David Guest nexusdb.public.support 2 21st March 2015 01:32 AM
OT Delphi Question Calculated Field David Guest nexusdb.public.discussions 4 19th March 2015 11:07 PM
TnxTable Calculated Field Dennis Esmonde-White nexusdb.public.support 1 5th April 2006 07:25 PM
Do I need a Calculated Field ? Henry - AR nexusdb.public.support 7 1st May 2005 02:48 AM
Create calculated field programatically Hugo Galindo nexusdb.public.support 3 1st April 2004 04:57 AM


All times are GMT +11. The time now is 08:22 AM.


Powered by vBulletin® Version 3.6.8
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.