Workbench ODBC - Dynamic query/parameter

Fatias
Fatias Member
in Workbench

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?

workbench_odbc.png

Thanks

Tagged:

Answers

  • GrantSmith
    GrantSmith Coach

    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

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • MarkSnodgrass
    MarkSnodgrass Coach

    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • MarkSnodgrass
    MarkSnodgrass Coach

    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • Fatias
    Fatias Member

    @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.

Categories