Workbench ODBC - Dynamic query/parameter

Fatias · 2021-07-14T17:23:08+00:00

There was an error rendering this rich post.

When we are establishing a new job in Domo Workbench of the type ODBC, we write the query in the box of Database Query Editor as in the image below.

However the query we want to do should not be static and we would like to modify that query based on external triggers.

For example, in the image, is it possible to pass a value to #FIELD# from a txt or csv file? Or to execute a sql file directly?

Thanks

Find more posts tagged with

SQL

Workbench

Accepted answers

All comments

GrantSmith

Hi @Fatias

Currently workbench doesn't support this sort of querying. The only thing it supports right now is a lastvalue (or previewonly) type parameters which will track the value in the last row of your dataset from your previous run and allow you to insert that into your query.

For more information refer to step 19 on https://domohelp.domo.com/hc/en-us/articles/360042932734-Connecting-to-ODBC-Data-in-Workbench-5

MarkSnodgrass

You don't have to write standard SELECT statements in the query window. You can execute stored procedures in the query window, which seems like what you might want to construct to allow for the dynamic nature that you are looking for. You can also write queries against views in case that would work for you.

MarkSnodgrass

You can also use the Lookup Table feature under Transformations to join and then filter the information.

https://domohelp.domo.com/hc/en-us/articles/360043437193-Combining-DataSets-in-Workbench-5

You might also consider doing this work in Domo in Magic ETL. It would reduce the workload on your system and you can leverage the speed and power of the Domo ETL tools.

Fatias

@MarkSnodgrass thanks for your feedback, it is good to know that we can use stored procedures in the query window.

However, in this case the value that I want to pass to the dynamic variable comes from an external source and that's why I was wondering if it would be possible to read (select) that value from a csv file to the dynamic variable or if it is possible to build the query in a sql file and run it in the query window.

Of course, one possibility would be to store that value in a temporary table in the database and then the query would be more simpler because would be only the join of two tables but I would rather avoid this workaround.