Post-SQL Statement hangs in Alteryx but works in Oracle Developer

Hello,

I have recently run into an issue with a Post-SQL statement in my output tool hanging up and failing to update as intended. The exact same statement runs very quickly in Oracle Developer without issue, but I need the statement to be scheduled with the rest of the workflow. Without the Post-SQL statement the workflow runs and updates the table without error. The screenshot below offers a simplified version of the workflow, showing how the workflow hangs up on the final step.

The SQL statement is a correlated update in which the output writes to a staging table where it can update a much larger table based on two fields. I have chosen this approach because none of the Output Tool's Update options work with composite keys. My update statement follows the same syntax as the one below, but with a few more variables being updated and a where clause with two conditions (because of the composite key).

SQL Syntax.PNG

I have tried a few different ways of ending the SQL query including semi-colon and backslash in various combos and positions but the only other result I have achieved is an Oracle error when attempting to end with "/" as works in Oracle Developer:

"ORA-00933: SQL command not properly ended"

I have done a significant amount of work in Alteryx and studied SQL in school, but do not have much experience working with enterprise SQL DB's through Alteryx. Any tips or suggestions would be greatly appreciated.

Thanks,

Mick G

Workflow

Error Message

Output

Database Connection

Accepted answers

MickG

I was able to resolve my issue:

Oracle SQL Developer is less case-sensitive than when Post Create SQL is used in Alteryx.

My temporary table was previously defined in Oracle Developer using lower case letters, causing issues when trying to reference the table with Post Create SQL. Once the table was dropped and added using all CAPS the flow worked as intended.

I hope this helps someone else having similar issues!

All comments

MickG

I was able to resolve my issue:

Oracle SQL Developer is less case-sensitive than when Post Create SQL is used in Alteryx.

I hope this helps someone else having similar issues!

Mavendano

Hi MickG,

I am running into a similar issue in Post SQL Create Statement in my output tool but with Teradata SQL. Essentially, I have a 'Block Until Done' tool before it hits the output tool to ensure that different data gets ingested into their respective database tables. The data gets ingested into it's table but the hold up occurs with the Post SQL script immediately after the records have been written to the database. Would you happen to know if this case sensitivity applies for all database warehousing platforms? Or could it be something else? Thanks!

-Mike

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703