A Tricky and a Challenging logic to be implemented

vnspn · Post by **vnspn** » Sun May 13, 2007 12:08 am

Hi,

We have a very tricky and a challenging logic to be implemented. We need to build some filter conditions dynamically. I would
explain this with an example here.

We have to apply some contraints on the records coming from the input. We have to build those contraints based on the data
fetched from the lookup table. The lookup table looks like this,

Busn_Id ----- Src_Ele ----- Oper ----- Cond_Val

10 ------------- STAT_CD ----- = ----- 25
10 ------------- DES_CD ------ = ----- 42
20 ------------- STAT_CD ----- = ----- 18
20 ------------ SRT_CD -------- > ----- AAA
20 ------------- END_CD ------ < ----- ZZZ

We will have the column "Busn_Id" in the Input records. We have to match it with the "Busn_Id" in the lookup table and get all
the filter conditions for that Input record from the lookup table. The values in the column "Src_Ele" in the above lookup table are all columns that are available in the Source link.

For example, the first record from the Source may have "Busn_Id" value 10. Then, I have 2 records that have the "Busn_Id" value
10 in the lookup table. The "Src_Ele" value it would return are STAT_CD and DES_CD. There would be columns in the Source link by these names as STAT_CD and DES_CD. So, we would have to build a filter condition which would be "STAT_CD = 25 and DES_CD = 42" for the first record. For the second record, if the "Busn_Id" is 20, then I have build a different set of contraints as per the values returned from the lookup.

So, its basically, we don't have a pre-defined set of constraints for all input records. Based on a key field value of the
incoming records, the contraint for it would differ. That too, on which column we have to apply the contraint it what we have to fetch from the lookup.

Please let us know if anyone of you have come across this approach or have any ideas / thoughts on how how could this be
implemented. Also let us know if there is any way we could do it in a parallel job if it could be done only in a parellel job. We might be using parallel job too in the near future.

Greatly appreaciate any of your help or suggestions!

Thanks.

JoshGeorge · Post by **JoshGeorge** » Sun May 13, 2007 6:06 am

Job 1: Fetch all the corresponding records from the look up and pivot the records to make your filter condition
Ex: (Busn_Id = 10 and STAT_CD = 25 and DES_CD = 42) or (Busn_Id = 20 and STAT_CD = 18 or/and SRT_CD > AAA and END_CD < zzz) as userstatus.

Main Job: Pass the user status as your filter condition

chulett · Post by **chulett** » Sun May 13, 2007 7:14 am

Your problem is this information won't work as a constraint as these are source field names and not the link names that the constraint would use. Perhaps if you standardized on link names and could 'pre-pend' that standard name to each field name? That could get messy, however, if the constraint gets... long.

You might want to consider trying to pull these 'filters' out and using them in your source query to filter the input rather than pulling 'everything' and then constraining the output.