Now that it's live, don't forget to accept your certification badge on Credly today! Learn more here.
We are currently experiencing an issue with Email verification at this time and working towards a solution. Should you encounter this issue, please click on the "Send Verification Button" a second time and the request should go through. If the issue still persists for you, please email support@alteryx.com for assistance.

Alteryx Designer Knowledge Base

Definitive answers from Designer experts.

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

gtorres8
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.
No ratings
Comments
Badrelouizi
6 - Meteoroid

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