character limit of tool Dynamic Output In-DB

Question

Hi, I tried to exported a SQL query of a workflow done by In-DB tools by the Dynamic Output In-DB tool. However,, due to the workflow is long and the output query is not complete in the brower. Do anyone what the character limit of query is for the output of Dynamic Output In-DB and how a complete query can be exported if the workflow is very long? Thanks

florijn · Answer

Thanks Sean.

Normally I do not create a long workflow in Alteryx In-DB. But the following screenshot shows one long workflow case I did recently. As I am not the SQL server administrator, I had to create some subqueries in the workflow to achieve the final table. The dynamic output tool as you shew cannot generate the full SQL script, and I guess there is a character limit even if I added a browser after I tried to optimize the workflow.

In the workflow left join tools and select tools are frequently used to find info from subqueries. A major problem for join and select tools is I cannot select the columns I need in the final table in the join tool, so I had to use a select tool following the join tool to select the columns I need. Otherwise the table will have too many columns after several joins. The SQL script for the above join and select steps will record to select all column names instead of * in the first join step and again then select the needed columns in the second select step.

I understand the script is recorded using WITH clause and CTE, but when I reviewed the script it can be shortened if join and select tools are combined in one tool as the normal join tool.

I finally had to mannually complete the exported script and run it successfully in the SQL server.

Hope Alteryx development team can make the characters as many as possible.

SeanAdams · Answer

Hey @florijn ,

You can see the query that is executed by using a Dynamic Output In-DB tool - there's an option there to specify that you want to see the executed query (screenshot below) and here's an earlier thread on this.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/View-SQL-Generated-by-In-DB-Tools/td-p/5682

As to the length of the workflow - most SQL databases don't have a maximum length of a query - it will just go slower and slower the longer it gets - so you'll need to invest in either breaking up your workflow into smaller chunks and/or indexing your data to make this perform.

TrevorS · Answer

Hello @florijn

Thank you for your contribution to the Community!

To help make it easier for others to assist, it would be helpful to get some additional information on your question. Here's what we recommend you include in your reply:

* What Alteryx Version are you using?
* Do you have a Workflow with sample data you can share?
* What about a Screenshot?
* How would one replicate the issue?
* Did you receive an Error Message?

Posting for Fastest Possible Solution (https://community.alteryx.com/t5/Alteryx-Community-Resources/Posting-for-Fastest-Possible-Solution/ta-p/178481)

Thank you very much!