InDB Macro Development Challenges: How to take dates?

[thedr9wningman]

Hi Community:

I am working in a pretty solidly InDB environment using Greenplum/PostGreSQL and I'm developing a macro so that I can save space in my workflows and make a global change to all my workflows (scheduled in Gallery and running daily) at once. I'm in a situation whereby I often have to make small changes and go to each of those seven (and growing) workflows routinely and manually. That introduces potential errors, so I want to develop a better, more robust development environment following on the advice of this post.

Here's my trouble: I'm not super strong in macro development, and I'm especially having trouble with the QA and development process of InDB macros. There are lots of issues at play--Alteryx translating SQL into PostGres, PostGres being finicky and difficult to work with, etc.

With all that stuff in mind, here are the details.

[deep breath]

OK, so I'm developing a macro that takes a population and compares it against another population. The three main inputs that the user defines are:

1. A comparison string
2. A dropdown for three potential values (defined by the business case)
3. A maximum date to consider (default is CURRENT_DATE)

The problem I'm running into is that the error messages I get from the workflow I'm using to test the macro itself.

So, it's complaining about the tool in the macro numbered 63.

I take that error message, and I throw it into a SQL editor, and I'm finding that the date I select is missing the straight-quotes required for the query to render appropriately.

More to follow.

Output from the results table in my testing workflow:

Error: Sendlog Filter (40): Tool #63: Error opening "SELECT * FROM (SELECT "person_id", "email_address", ....(2020-01-31) AS "date_of_enquiry" FROM ....
WHERE "send_date" <= "date_of_enquiry"::Date": No Columns Returned.

With a little polish, we rip out the error message and the enclosing quotes and arrive at:

SELECT * FROM (SELECT "person_id", "email_address", "email_name", "send_date", "brand", "campaign_name", (2020-01-31) AS "date_of_enquiry"... 

So here's the problem:

In my workflow, I can select a date, but as we see above, that date lacks the necessary single-quotes. 

Instead of (2020-01-30) AS "date_of_enquiry" I need ('2020-01-30') AS...

So, that leads me to trying to manually add those: 

 Logically, this works in SQL. But what I get out of Alteryx is a failure: 

(SELECT [...]
('\''||"date_of_enquiry"||'\'') AS "date_of_enquiry" 
FROM (SELECT [...] 
(2020-01-30) AS "date_of_enquiry" FROM (...))
WHERE "send_date" <= "date_of_enquiry"::Date

I seem to be incapable of getting around this error. I've looked up several ways to escape quotations, etc. but I'm stumped. 

Why is Alteryx InDB rendering (2020-01-30) from my date selection instead of ('2020-01-30')?

Thanks for your help!

[thedr9wningman · Accepted solution]

In true there's-always-one-crazy-guy-on-the-bus-but-I-don't-see-anyone-else-on-the-bus-who's-crazy I may have found the solution.

[finishes talking to self]

The replace-string portion of the action has been my saviour here. 

Here's what I did:

1. I ticked the 'replace a specific string' portion of the action tool
2. The whole phrase in the originating formula tool has the required straight-quotes on it, so I'm telling it to replace the replaceme part of that string
3. That seems to make the substitution I'm looking for. 

I'll show another example with the date part: 

This seems to retain the explicit quotes I put in and only replaces the specific string I determine, in this case, the date. The remaining quotes and date casting are left behind, so I can change the actual date with user input.