Problem I'm Trying to Solve with Alteryx
I have multiple queries I have to manually run every morning. They update datasets that the team relies on, and I would like to automate them to run every morning without manual involvement from me.
Technical Setup
We are running Teradata 16.2 and Alteryx Designer 2021.3.2.54175.
The queries aren't huge, but they are on the larger side (1k - 2k lines). They combine data from multiple tables (all Teradata) and involve a not-insignificant amount of data transformations and joins to get into their final form. Once the final dataset is created, the table it will replace is dropped and recreated with the new dataset.
The complexity of the queries are managed by using volatile tables (10 - 20 per query). These all run perfectly well when run as a continuous statement in Teradata Studio. In Alteryx, I'm running most of the SQL in the Input Data step's Pre SQL Statement, using volatile tables to store data as needed, and ultimately combining the volatile tables into a final volatile table that will be pulled in the Input Data step's SQL Statement.
Here is the structure all my volatile tables use. Occasionally I use CTEs within the creation of the volatile table.
CREATE MULTISET VOLATILE TABLE VT_Name
AS
(
SELECT *
FROM DATABASE.DATATABLE
)
WITH DATA
ON COMMIT PRESERVE ROWS;
Alteryx Issue
The Pre SQL Statement never runs to completion. I keep getting one particular error.
3932 Only an ET or null statement is legal after a DDL Statement.
The odd thing about this is that I can create one, two, or sometimes three volatile tables before the error happens. There is nothing different about the syntax used to create any of the volatile tables. However, once I added the fourth volatile table's code into the Pre SQL Statement, the error starts appearing.
Does anyone have any insight into how I can solve this issue?
Thanks!
