I have a workflow that runs multiple components. In the workflow I have a parameter called load_id that I would like to use to query all kinds of tables later in the flow. But if you use a JDBC Reader in your component you can't use the parameter in the query. You could if you were able to do an Alter Format before the JDBC Reader, but JDBC Reader allows no input.
I've seen some tips here in the Community, like these:
But initially I still ran into some issues before I got it working. So I decided to share a working example.
This is my very simple workflow.
It only has a Run DQC task that will run the component.
But let me show the global variables in my workflow. I hardcoded the load_id, so my query would get rows back.
Now lets look at the configuration of the Run DQC task:
As you can see, I run the parameterinquery.comp component and pass on the load_id with the expression """"+load_id+"""" .
On to my component:
The Random Record Generator is the start of this, because with the JDBC Reader gone we got to start somewhere. I don't use the column it creates.
In the Alter Format you need to add load_id and map it as a parameter:
Now let's look at the SQL Select step:
Here you can see the load_id parameter being used in the query.
The thing that is quite different from the JDBC Reader, is the column mapping.
For some reason the Data Columns are invalid. And what are Data Columns anyway?
Well it turns out that you can solve this problem by adding the columns in the Alter Format before the SQL Select.
Now all columns in the SQL Select are valid and we can run the workflow.
I've written the SQL output to a Text Writer and here it is, only rows related to this load_id:
And that makes my solution a lot more robust, knowing I'm only querying data from the load_id of the workflow run I'm working on.