Passing datas from hashed files to the where clause

[bala_135]

Hi All,

Is there a way of passing the values from the hashed file to the Where clause of the sql.


Let me explain
1.My source system is like this
table1------table2
table1-------table3
table1 -------table4.
All are related through primary key(table1 pk in table2,table3,table4)

Filter condition for my specfic requirement fetches only 2 rows from my first table1.So i am storing it in the hash files.
I want to generate hash files for table2,table3,table4 based on the filter condtion of table1 which i am using it latter for integration.

Fetching all the records from table2,table3,table3 and creating hash files by looking up with table1 solves the requirement but i am extracting again and again the same millions and millions of rows.

No CDC logic as the system is not timestamped anywhere.

If i pass these values to my table 2 in where clause then only those records from table 2,table3,table4 are going to be feteched and then i can hash it.

Any inputs most appreciated.

Regards,
Bala.

[chulett]

It would need to be in a Job Parameter to be able to use it in a query, so you'd need something to get it from the hashed file out somewhere it could be picked up from - a flat file, perhaps or $UserStatus.

[swades]

Or try UtilityHashLkp - gerenate DUMMY key as lkp key.

[bala_135]

Hi,

Thanks for the inputs.
Let me give my approach.
Enviornment
1.Using parameter.ini file to set the parameter values.
2.No datastage sequence.External scheduler.

My approach,

Filter comes from one table.Need to separate the filters using conditions

As craig suggested i am going to read the inputs from the sequentail file(i can stage the values again into seq file )
Separate them using the field function.
loop each input.
separate the filters which are needed in the where clause
concatenate using a comma separated delimter and put the entire string into the a variable.
Set the parmater value to this varibale.
Call the job paramater into the where clause.

Concerns.
I wish to do all this in the Job control of the same job and finally use the DSSetParm(DSJ.ME,"ParmName","Concatenated string") and set the where clause parameter value but could not do it.Need to create a dummy job and call this job.

I guess with this approach the business dynamicity(i mean the changing filters) is encapsulated and no probs for the other paramters like the user id,password can come from the parameter.in file.I will not even include the where clause parameter in the .ini file.

Any other inputs to this approach or better approach.

Thanks in advance.

Regards,
Bala.