Hi all:
I'm having a very frustrating problem and I need help.
I'm trying to create a dummy variable for a theoretical time period so I can go 'back in time' and look at logs. I'm using tables that have date fields.
The code that is generated from my INDB output is as follows:(the where clause is the important part here)
SELECT
*
FROM
(SELECT
brand
,effective_date
,termination_date
,('2019-12-29'::date) AS date_of_enquiry
FROM
(SELECT
brand
,effective_date
,termination_date
FROM
(SELECT * FROM [source table])
AS a ) AS a ) AS A
WHERE effective_date::DATE <= Date_of_enquiry::DATE
This successfully runs in my Greenplum editor. But every time I run it in Alteryx, I get an error right at the where clause:
I don't understand what the problem is. The date types are timestamp data fields (rather than string), and the variable I'm creating is a datetime. What am I doing wrong?!??! I've had this problem in other workflows, but casting things to ::date helped.
Any assistance would be amazing.
Cheers,
Cedric
Solved! Go to Solution.
And obviously, I've tried this with and without the date casting.
If I leave the casting out of the filter, things now seem to be working... ugh.
Also, case seems to matter in Alteryx, but not in my SQL editor for the names of the fields.
I guess I've answered my own question. Whee.