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:
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!
Solved! Go to 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:
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.