on 05-09-2016 08:18 AM - edited on 07-27-2021 11:41 PM by APIUserOpsDM
Have you ever wondered what query was being sent to your database at a certain point in your workflow?
You can view this using a Dynamic Output In-DB tool! All you have to do is place a Dynamic Output tool after any tool in your In-DB process in order to see the query that is being sent to the database. Once the tool is placed in the desired spot in your workflow, make sure that you have the "Query" option selected in the Dynamic Output tool's configuration:
You can now run the workflow and since the output tab of the Dynamic Output tool is a normal connection tab, you will be able to view the query using the browse everywhere feature and the results window. If you are interested in viewing the entire query and/or copying and pasting it you will have to add a browse tool after wards so you can access the query string in the Cell Viewer:
A "fun fact" with this process is that you can take the query that is produced by the Dynamic Output tool and paste it into the SQL Editor of a normal Input tool to produce the same results:
Is there a way to dynamically change the where clause in the tool 'Connect in-DB' like that of the 'Dynamic Input' under Developer tab? If using 'Dynamic input' tool is the only way out, then I find that Dynamic input takes much longer and hence not convenient. The In-DB tools have much higher running speed.
Hi Abinaya, There isn't a "out of the box" tool that can do this, but you could build a batch macro to update a where clause in the Connect In-DB tool. For example, I have an In-DB process connected to a table that has every city by state in the US. My where clause is initially searching for just the City of Glen Ellyn:
What you can do is turn this into a batch macro with the action tool updating the portion of the where clause you are interested in. In my case, just the city name:
The downside to this is that you will have to stream your data out of the In-DB tools. The In-DB macro output tool can only be used in a standard macro. So to use this in a batch macro, you will need to use the data stream out tool and connect a macro output. An Alternative would be to have your batch macro contain your entire process so that you can keep it In-DB.
Once I have completed the macro, I can insert it into a workflow. In my workflow I have provided a list of Cities that I was to update my initial where clause with:
Once run, the list of Cities I provided updated the original city listed in the where clause and the output data shows the results for each of the newly provided cities:
@SR The Dynamic Input In-DB tool will do this. It accepts the Connection Name and Query as Input so the query could be manually constructed using any of the regular Alteryx tools like a formula tool and then passed to the Dynamic Input In-DB tool. Here's a screenshot of an example. I haven't modified the where clause here but you could easily do that with a Formula tool.
Hi Andy,
In your screen shot, I see you have connected your tool Dynamic input in- DB with File input. I am trying to connect similar way to a file and then connect the output to a DB join operation ( with the output from a DB table data source) but I could not do it, please share your thought if this is possible to do. Why I am doing is that, I dont want to create a temporary table in DB ( assume I dont have write previlege in DB) , in that case i want to join data in csv with another table from database and create the output. The workflow is giving error as "unable to find connection-2" .