Alteryx Designer Knowledge Base

Definitive answers from Designer experts.
Don't forget to submit your entry for the Excellence Awards by October 30! | Need more information about the program? Check out the blog here

In-DB tools add a SELECT statement to Common Table Expression (CTE) queries

Alteryx
Alteryx
Created

Environment Details


The Connect In-DB and Dynamic Output In-DB will add a "SELECT * FROM" to a Common Table Expression (CTE) query. There is no error, but eventually, if they use a tool that pulls the query, it will have the incorrect syntax "SELECT * FROM WITH..."

 

For example:

 

In the Connect In-DB using an INDB file with the query:

 

WITH employee AS (SELECT * FROM Employees)
SELECT * FROM employee WHERE ID < 20
UNION ALL
SELECT * FROM employee WHERE Sex = 'M'

 

When putting in a Dynamic Output In-DB to show the query and it's seen that the SELECT * FROM is added to the original query.

idea Skyscrapers

 

  • Alteryx Designer
    • All versions
  • CTE queries


Cause


CTE queries are not supported within the In-DB tools.



Resolution


Use the Input Data tool where possible in the workflow as CTE queries are read as is to pull in data.
 

Additional Information

  • An idea to support CTE queries for the In-DB tools has been created. Click here to up-vote for this idea to be implemented in a future release of Designer.
  • Alteryx Discussion.
  • Possible errors in a workflow when using CTE queries in the In-DB tool:
    • Error: ORA-00903: invalid table name
    • The In-DB Dynamic Input tool cannot accept a query which uses a WITH clause. Consider using a Query Alias List instead.
Comments
6 - Meteoroid

This is genius. could you share the rest of the workflow to how to pull the data and see the output table?