Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

INDB Date comparisons not working (Greenplum connection)

Highlighted
Alteryx Partner

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:

 

thedr9wningman_0-1578523707725.png

 

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

Highlighted
Alteryx Partner

And obviously, I've tried this with and without the date casting. 

Highlighted
Alteryx Partner

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. 

 

Labels