Hi community!
Today, I have a quick tip to share on how to pass a dynamic query into a JDBC reader. If you are using ONE Desktop as part of your day-to-day this would be a good practice to implement when it comes to using the JDBC reader step.
Overview
The following is a working example of passing a dynamic query into a JDBC reader. Its overall structure consists of two plans that are run sequentially in a workflow. The idea is that the first plan will produce the query that the second plan (that includes our main logic) will use.
In the above example, an audit table is read in the second DQC plan, but with a query that depends on information on the current state of the table (for example, the maximum of a particular record, record count, and latest refresh date).
First Plan - Retrieving Queries
In the first plan, functions and variables are applied to produce a .sql file that contains the JDBC reader query to be used.
As an example, functions can include getting the current date to apply a filter to the final query, variables can include values retrieved from a query that checks the current state of the database table (for example, a query to get the count of records or latest refresh date).
An example of how this plan can look is the following:
In the first step (“current table information”), any needed information on the current table data is read. As an example below, the latest refresh date for the table is retrieved, as well as the current number of records, stored in separate columns.
The “set record limit” is an example of an alter format step that creates any fields that need to be parameterized and included in the query. (Such as a record limit in this case).
The following generate query step produces the query that the second plan, which has our main logic, will use. See example below:
Finally, this field containing the query is written to a .sql file:
In the second plan, where we intend to use this dynamic query for our logic, we can now use this file in the JDBC reader step (note the query string in the JDBC reader step’s general tab can be left blank).
Hope you found it useful! Please let me know if you have any questions or any tips you’d like to share below in the comments