Hi Drams,
interesting use case. Let me split the answer in a few bullet-points:
- There are no “WHILE” loop options in Ataccama (as in programming languages), however, you can rethink about your scenario as an FOR loop or FOR-EACH loop that could be doable using an Orchestration workflow and the Iterate Task
- You can’t define any loops in Ataccama plans or components
- You can define a parametrized query (SQL Select) to a JDBC reader as long as the output result set is always the same (e.g. count of lines and a table/view name as a string literal)
- For your scenario you can think of a 2 workflows using the Iterate task mentioned above with a SQL Row iterator (query your DB metadata to get the list of views you need to check) and then have a child workflow (triggered by the iterator) to execute the actual component using the Run DQC task. You can store the results from the component into a DB table or some “append-friendly” store
Hope this helps at a high-level. For more help you can reach out to our professional services to deliver the implementation for you.
Hi @Pele We have a similar use case and I have a question on the above:
On Point 3: Does Ataccama allows parameterizing whole query? As far as my testing, I could only parameterize part of the query and not the whole query in SQL Select or SQL Execute. So the point 3 to found counts will not work. Can can you clarify?
Hi Siva,
it is possible, and we sometimes use it like that, to have a DERIVED variable in a workflow that represents the SQL query string. Since derived variables in workflows work with expressions, you can achieve pretty complex logic to build your queries even combining other derived or input workflow variables.
Once your derived variable is ready, you can pass it to the Run DQC workflow task as a parameter - pay special attention on which workflow tasks work with expressions and/or semi-expressions so you set the parameter mapping using the right syntax (check our online documentation for more details).
If your query result set is similar to what I described in my previous post, you should be easily able to map the JDBC Reader step’s Query field as parameter (it would look like the query field is actually empty, but there’s a little icon next to the “Query” text indicating the parameter mapping).
Finally, the query result set needs to map to your JDBC reader step columns that are expected to be in the SQL output.
Same would apply to SQL Select step.
Hope this clarifies.
