Passing multiple values to the 'SELECT' query in ODBC stage

[yaminids]

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

[DS_SUPPORT]

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

Code: Select all

SELECT #DYNAMICSELECT# FROM YOUR_TABLENAME;

and now pass the value for the job parameter (DYNAMICSELECT) like

Code: Select all

COLUMN1 || '|' || COLUMN2 || '|' || COLUMN3

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.

[yaminids]

thanks a lot for the information.
As a follow up question, is there a way to pass values in the 'WHERE' clause during run time

Thanks
Yamini

[chulett]

Same way, basically. Include a job parameter for the string and put it in your where clause surrounded by hash marks.

[yaminids]

Craig,

The job is running fine if there is only one value but is failing when there are multiple values in the file.
The data in the file is as follows
'1',
'2'

Any ideas how can I parse the file to input the values as '1','2'?

Thanks
Yamini

[yaminids]

Craig,

The job is running fine if there is only one value but is failing when there are multiple values in the file.
The data in the file is as follows
'1',
'2'

Any ideas how can I parse the file to input the values as '1','2'?

Thanks
Yamini

[kduke]

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.

[DS_SUPPORT]

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.

[yaminids]

Thanks a lot for the information. I will try all the scenarios and post the results here