View Single Post
  #6  
Old 5th April 2019, 08: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