Passing multiple values to the 'SELECT' query in ODBC stage
Moderators: chulett, rschirm, roy
Passing multiple values to the 'SELECT' query in ODBC stage
Friends,
I am trying to design a job which selects data from a table based on the values passed in the query. The values to be passed are stored in a flat file on DS server.
Is there a way to pass values to the select query in ODBC stage during the run time?
Thanks a lot in advance
Yamini
I am trying to design a job which selects data from a table based on the values passed in the query. The values to be passed are stored in a flat file on DS server.
Is there a way to pass values to the select query in ODBC stage during the run time?
Thanks a lot in advance
Yamini
-
- Premium Member
- Posts: 232
- Joined: Fri Aug 04, 2006 1:20 am
- Location: Bangalore
You can actually do it, but it will be like concatenation of all the columns to a single column, and you have to decide in your further transformations how you want to split it. And lot of work has to be done for the Select statement also.
Define a Job parameter , say DYNAMICSELECT. Define a single column alone in the columns tab and give the data type as Varchar with a maximum length and change the query to User Defined SQL.
Write your query as
and now pass the value for the job parameter (DYNAMICSELECT) like
The idea is , you have to concatenate all the columns with some delimeters, where you can split them inside the job. || specifies the concatenation in Oracle, and it differes for each databse, for Sql Server it will be +.
And you have convert all the Numeric and Date columns to Char data type, then only you will be able to concatenate them.
And construct the value for DYNAMICSELECT in the Sequence job and pass it to the server job.
Define a Job parameter , say DYNAMICSELECT. Define a single column alone in the columns tab and give the data type as Varchar with a maximum length and change the query to User Defined SQL.
Write your query as
Code: Select all
SELECT #DYNAMICSELECT# FROM YOUR_TABLENAME;
Code: Select all
COLUMN1 || '|' || COLUMN2 || '|' || COLUMN3
And you have convert all the Numeric and Date columns to Char data type, then only you will be able to concatenate them.
And construct the value for DYNAMICSELECT in the Sequence job and pass it to the server job.
This code is a part of EtlStats. There is a job included which will take any SQL statement and turn it into html document and email it as an attachment. Kind of poor man's reporting tool. There are bunch of reports which included in folder called SqlScripts. They have :1, :2 as parameters. One of the parameters is the values for these. The default separator is '~'. So if I want to send Kim~Duke then Kim replaces :1 in the SQL statement. Duke replaces :2. The whole SQL statement is one parameter.
There is a job which calls this job and it will read the SQL from SqlScripts so all you need to give it is the filename for the SQL script. SqlScripts gets loaded into a project if you follow the instructions for installing EtlStats. Most of the reports are for reporting row counts of a sequence or all jobs run today or all last runs of all jobs. They sort in different ways like by job name or date or elapsed time. So you can see all your long running jobs.
There is a job which calls this job and it will read the SQL from SqlScripts so all you need to give it is the filename for the SQL script. SqlScripts gets loaded into a project if you follow the instructions for installing EtlStats. Most of the reports are for reporting row counts of a sequence or all jobs run today or all last runs of all jobs. They sort in different ways like by job name or date or elapsed time. So you can see all your long running jobs.
Mamu Kim
-
- Premium Member
- Posts: 232
- Joined: Fri Aug 04, 2006 1:20 am
- Location: Bangalore
Easiest way should be , store the value as you need, because anyway someone has to create the parameter file, you can istruct them how you want to read the parameter and ask them to store in the same format.
or you need to custom routine, where you need to parse all the values and construct the parameter.
or you need to custom routine, where you need to parse all the values and construct the parameter.