Query with in db tools

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

Workflow

Datasets

Developer

Transformation

In Database

Data Investigation

Database Connection

In-Database

Accepted answers

ImenMersani

Hello,

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

All comments

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-Expressions-in-Custom-SQL/idi-p/176518
(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/category%20in%20database 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

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

Quick Links

Unanswered
Community Events
Groups

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703