Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

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

MickG
5 - Atom

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.

 

AlteryxIssue.PNG

 

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

3 REPLIES 3
MickG
5 - Atom

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!

Mavendano
5 - Atom

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

MickG
5 - Atom

Hi Mike,

 

Unfortunately I am not familiar with Teradata SQL, so I cannot say for sure. But, I was constantly running into errors with my Oracle SQL connections until I began using caps just about everywhere in my SQL references and statements. However, it is SOP for my company to use all caps in DBs.

 

My understanding is that the direct developer programs are better at making connections through mismatched character case/missing schemas etc., but due to the way Alteryx passes SQL it is pickier, and needs to be referenced exactly as the DB Tables were defined. So understanding how the tables were created is key to correctly using them through Alteryx.  I also had to add the schema at the begging of my table reference (select from schema.table rather than select from table) - but I have no knowledge of Teradata schemas so I again cannot say for certain if this will help.

 

If you could provide some more detail I may be able to provide a better answer. What is the error Alteryx is returning? Does the structure of the tables differ? What is the purpose of the Post-Create SQL statement?

 

- Mick

Labels