We are celebrating the 10-year anniversary of the Alteryx Community! Learn more and join in on the fun here.
Start Free Trial

Alteryx Designer Desktop Discussions

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

Common Table Expressions (CTEs) in Connect In-DB Tool

deschuteswiebe
5 - Atom

I have a complex SQL query that involves multiple CTEs that produces results in SSMS with no errors.  I am trying use the In-DB Tool to build a workflow around this query, and I am receiving an error message about the "WITH" statement for my CTE. My query does not lend itself well to using subqueries instead of CTEs, so I am looking for a solution that accommodates CTEs. 

 

I have seen previous questions about CTEs but nothing that provided a solution, and I am hoping something has changed since the last response 3 years ago. 

  1. Does Alteryx support CTEs? 
  2. If so, what is the correct tool and syntax?
  3. If they are not supported are there any workarounds? 

 

Here is a simplified example of my current query.  

 

WITH product_A AS (SELECT PRODUCT_ID 
FROM product
WHERE PRODUCT_CATEGORY = 14)

 

SELECT *
FROM transactions
INNER JOIN product_A
ON transactions.PRODUCT_ID= product_A.PRODUCT_ID
WHERE tranactions.TRANS_DATE >'2/1/2023'

 

1 REPLY 1
apathetichell
20 - Arcturus

Regular input data supports CTE in the data connection/odbc mode. In-DB does not. I'd do something different with macros...

Labels
Top Solution Authors