#1  
Old 20th November 2018, 11:37 AM
AJM AJM is offline
Junior Member
 
Join Date: Nov 2018
Posts: 4
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: 184
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: 4
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: 184
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).
  #5  
Old 5th April 2019, 06:22 PM
AJM AJM is offline
Junior Member
 
Join Date: Nov 2018
Posts: 4
Default

Hi Eivind,

Sorry for slow reply. Please try this script for testing.

CREATE TABLE PEOPLE
(
NAME VARCHAR(10) NOT NULL,
JOINED DATE NOT NULL
);
INSERT INTO PEOPLE VALUES ('A', DATE'2019-04-01');
INSERT INTO PEOPLE VALUES ('B', DATE'2019-04-02');
INSERT INTO PEOPLE VALUES ('C', DATE'2019-04-03');
INSERT INTO PEOPLE VALUES ('D', DATE'2019-04-01');
INSERT INTO PEOPLE VALUES ('E', DATE'2019-04-03');


Query : Will return a result set
SELECT JOINED, COUNT(*) as Accounts
FROM (select JOINED + interval '9' day as JoinDate, * from PEOPLE p
WHERE
(p.JOINED + interval '9' day > DATE'2019-04-10')) p
GROUP BY JOINED


Query : Will return no results when using :Days = 9

SELECT JOINED, COUNT(*) as Accounts
FROM (select JOINED + interval :Days day as JoinDate, * from PEOPLE p
WHERE
(p.JOINED + interval :Days day > DATE'2019-04-10')) p
GROUP BY JOINED
  #6  
Old 5th April 2019, 09:13 PM
Thorsten Engler [NDA]
 
Posts: n/a
Default Re: Grouping on a function calculated field

You can't just replace part of a literal with a parameter.

AJM wrote:

>
> Hi Eivind,
>
> Sorry for slow reply. Please try this script for testing.
>
> CREATE TABLE PEOPLE
> (
> NAME VARCHAR(10) NOT NULL,
> JOINED DATE NOT NULL
> );
> INSERT INTO PEOPLE VALUES ('A', DATE'2019-04-01');
> INSERT INTO PEOPLE VALUES ('B', DATE'2019-04-02');
> INSERT INTO PEOPLE VALUES ('C', DATE'2019-04-03');
> INSERT INTO PEOPLE VALUES ('D', DATE'2019-04-01');
> INSERT INTO PEOPLE VALUES ('E', DATE'2019-04-03');
>
>
> Query : Will return a result set
> SELECT JOINED, COUNT(*) as Accounts
> FROM (select JOINED + interval '9' day as JoinDate, * from PEOPLE p
> WHERE
> (p.JOINED + interval '9' day > DATE'2019-04-10')) p
> GROUP BY JOINED
>
>
> Query : Will return no results when using ays = 9
>
> SELECT JOINED, COUNT(*) as Accounts
> FROM (select JOINED + interval ays day as JoinDate, * from PEOPLE p
> WHERE
> (p.JOINED + interval ays day > DATE'2019-04-10')) p
> GROUP BY JOINED


  #7  
Old 5th April 2019, 09:28 PM
Paul Toms
 
Posts: n/a
Default Re: Grouping on a function calculated field

Another variation on the highly desirable enhancement documented here,
then https://www.nexusdb.com/mantis/view.php?id=2122 ?

I'm sure the INTERVAL implementation in NexusDB conforms to an SQL
standard, but why would anyone come up with that standard? Its a PITA!

On 05/04/2019 10:13, Thorsten Engler [NDA] wrote:
> You can't just replace part of a literal with a parameter.


  #8  
Old 5th April 2019, 10:00 PM
Thorsten Engler [NDA]
 
Posts: n/a
Default Re: Grouping on a function calculated field

Paul Toms wrote:

> Another variation on the highly desirable enhancement documented here, then
> https://www.nexusdb.com/mantis/view.php?id=2122 ?
>
> I'm sure the INTERVAL implementation in NexusDB conforms to an SQL standard,
> but why would anyone come up with that standard? Its a PITA!
>
> On 05/04/2019 10:13, Thorsten Engler [NDA] wrote:
> > You can't just replace part of a literal with a parameter.


I have plans to add a INTERVAL() function that can construct an interval from
an expression. But for that I'll first need to add interval as a real field
type.

NexusDB currently doesn't actually have an interval field type, you can only
have INTERVAL literals as part of a date time expression, but INTERVAL can't
exist outside of that.

  #9  
Old 6th April 2019, 03:39 AM
Micha√ęl HUNAULT
 
Posts: n/a
Default Re: Grouping on a function calculated field

Le 05/04/2019 √* 11:13, Thorsten Engler [NDA] a √©crit¬*:
> You can't just replace part of a literal with a parameter.
>
> AJM wrote:
>
>>
>> Hi Eivind,
>>
>> Sorry for slow reply. Please try this script for testing.
>>
>> CREATE TABLE PEOPLE
>> (
>> NAME VARCHAR(10) NOT NULL,
>> JOINED DATE NOT NULL
>> );
>> INSERT INTO PEOPLE VALUES ('A', DATE'2019-04-01');
>> INSERT INTO PEOPLE VALUES ('B', DATE'2019-04-02');
>> INSERT INTO PEOPLE VALUES ('C', DATE'2019-04-03');
>> INSERT INTO PEOPLE VALUES ('D', DATE'2019-04-01');
>> INSERT INTO PEOPLE VALUES ('E', DATE'2019-04-03');
>>
>>
>> Query : Will return a result set
>> SELECT JOINED, COUNT(*) as Accounts
>> FROM (select JOINED + interval '9' day as JoinDate, * from PEOPLE p
>> WHERE
>> (p.JOINED + interval '9' day > DATE'2019-04-10')) p
>> GROUP BY JOINED
>>
>>
>> Query : Will return no results when using ays = 9
>>
>> SELECT JOINED, COUNT(*) as Accounts
>> FROM (select JOINED + interval ays day as JoinDate, * from PEOPLE p
>> WHERE
>> (p.JOINED + interval ays day > DATE'2019-04-10')) p
>> GROUP BY JOINED

>

Hi,

for me the result is correct and the same with a litteral or a parameter
(v4.5013 - 32bits).

Joined Accounts
2019-04-11 1
2019-04-12 2

INTERVAL seems to handle correctly string parameters.


--
Micha√ęl

  #10  
Old 8th April 2019, 08:36 PM
Roberto Nicchi
 
Posts: n/a
Default Re: Grouping on a function calculated field

Il 05/04/2019 17:39, Micha√ęl HUNAULT ha scritto:
> Le 05/04/2019 √* 11:13, Thorsten Engler [NDA] a √©crit¬*:
>> You can't just replace part of a literal with a parameter.
>>
>> AJM wrote:
>>
>>>
>>> Hi Eivind,
>>>
>>> Sorry for slow reply. Please try this script for testing.
>>>
>>> CREATE TABLE PEOPLE
>>> (
>>> NAME VARCHAR(10) NOT NULL,
>>> JOINED DATE NOT NULL
>>> );
>>> INSERT INTO PEOPLE VALUES ('A', DATE'2019-04-01');
>>> INSERT INTO PEOPLE VALUES ('B', DATE'2019-04-02');
>>> INSERT INTO PEOPLE VALUES ('C', DATE'2019-04-03');
>>> INSERT INTO PEOPLE VALUES ('D', DATE'2019-04-01');
>>> INSERT INTO PEOPLE VALUES ('E', DATE'2019-04-03');
>>>
>>>
>>> Query : Will return a result set
>>> SELECT JOINED, COUNT(*) as Accounts
>>> FROM (select JOINED + interval '9' day as JoinDate, * from PEOPLE p
>>> WHERE
>>> (p.JOINED + interval '9' day > DATE'2019-04-10')) p
>>> GROUP BY JOINED
>>>
>>>
>>> Query : Will return no results when using ays = 9
>>>
>>> SELECT JOINED, COUNT(*) as Accounts
>>> FROM (select JOINED + interval ays day as JoinDate, * from PEOPLE p
>>> WHERE
>>> (p.JOINED + interval ays day > DATE'2019-04-10')) p
>>> GROUP BY JOINED

>>

> Hi,
>
> for me the result is correct and the same with a litteral or a parameter
> (v4.5013 - 32bits).
>
> Joined¬*¬*¬*¬*¬*¬* Accounts
> 2019-04-11¬*¬* 1
> 2019-04-12¬*¬* 2
>
> INTERVAL seems to handle correctly string parameters.
>
>



I see the problem here (tryed both 32 and 64 bit server ver 4.5013).
Using parameters the result is empty.

But i see something more: the query that works (no parameters) returns
the following wrong result:

2019-04-02 1
2019-04-03 2

instead of the expected result

2019-04-11 1
2019-04-12 2

Roberto


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 02:14 AM.


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