Query with in db tools

[ImenMersani]

Hello,

I'm trying actually to execute a query with Alteryx in-db tools (), but it doesn't work.

PS: the query starts by "with table_name as (SELECT....."

The WITH clause can help you write readable SQL queries and break complex calculations into logical steps. It was added to SQL to simplify complicated long queries.

Any suggestions please?

Thank you 

[DataNath]

Hey @ImenMersani, can you try wrapping your query in 'SELECT * FROM (<YourCurrentQuery>)'? By default, the resulting In-DB query works by making a CTE of each tool and bringing them all together and so writing CTEs within tools isn't supported as that would result in 'WITH TOOL_ID AS (WITH TABLE_NAME AS....)' and cause a syntax error.. Hopefully wrapping in the above way can help but if not you may need to look at reformatting with subqueries etc.

[simonaubert_bd]

Hello @ImenMersani 
"With " clause also known as Common Table Expression (CTE) is not supported in Alteryx in-db. What I can propose :
-create a view outside Alteryx and then query the view with a select in Alteryx
-add your vote to this idea https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Ideas/In-DB-to-Allow-Common-Table-Expressi...
(by the way, if you're in in db tools, have a look to all ideas https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Ideas/idb-p/product-ideas/label-name/categ... we really need some traction on that)
Best regards,

Simon

[Aryasuta_Panda]

Hi, you can create your query without "WITH" clause and create the calculations in Alteryx tools (In DB Tools as well). I have found it the better way if any sql query is not working properly.

Thanks.

[ImenMersani · Accepted solution]

Hello,

I am grateful for your answers. In the end, I came up with a solution by using nested SQL queries.

[Aj4151987]

I'm interested if you can message me