Lookup/join using something other than equal
Posted: Tue May 12, 2015 9:38 am
I am having a problem with doing a lookup where the value is <. I have searched the forum and found some suggestions but they do not apply to my situation.
DataStage stream should look like:
Source ======> Lookup1 DS========> transform =======> Lookup2 DS =======> Target
Source: gets data by joining a number of tables.
Lookup1: the SQl to generate Lookup1 calculates values using ROUND, SUM and Case to create the keys plus numeric values.
transform: Transforms the numeric values from Lookup1 depending on certain criteria
To this point I am Ok.
The problem is in Lookup2. It needs to take one of those transformed numeric values and do a lookup using > and <
LKP_MIN_VALUE<=SRC_VALUE3
AND LKP_MAX_VALUE>=SRC_VALUE3
The range option on the lookup stage is very limiting
I tried with the range on the lookup hoping to trick it with:
LKP_MIN_VALUE<=SRC_VALUE3
AND LKP_MIN_VALUE<=SRC_VALUE3
But it doesn't like the fact that I am using LKP_MIN_VALUE twice. it want a range for LKP_MIN_VALUE but I only want LKP_MIN_VALUE<=SRC_VALUE3
It is not possible to move the three Selects into one source select. The source and first lookup select are pretty big and the code for the transform would also need to be incorporated.
Using a Join stage joining on a dummy field then a filter to check the > and < works except there are 2.3 million records in the source and luckily only 83 in the lookup, but it still creates 197 million records to be filtered.
I could do the source and lookup1 in one job and write to a working table. Then join the working table to lookup2 but that would require a DBA and I am sure they would not be happy creating a working table for this issue since there may be many in the future.
What I find strange is this is really simple to do using a server job.
Does any one know some simple trick to do a lookup using >= without having to over generate records?
DataStage stream should look like:
Source ======> Lookup1 DS========> transform =======> Lookup2 DS =======> Target
Source: gets data by joining a number of tables.
Lookup1: the SQl to generate Lookup1 calculates values using ROUND, SUM and Case to create the keys plus numeric values.
transform: Transforms the numeric values from Lookup1 depending on certain criteria
To this point I am Ok.
The problem is in Lookup2. It needs to take one of those transformed numeric values and do a lookup using > and <
LKP_MIN_VALUE<=SRC_VALUE3
AND LKP_MAX_VALUE>=SRC_VALUE3
The range option on the lookup stage is very limiting
I tried with the range on the lookup hoping to trick it with:
LKP_MIN_VALUE<=SRC_VALUE3
AND LKP_MIN_VALUE<=SRC_VALUE3
But it doesn't like the fact that I am using LKP_MIN_VALUE twice. it want a range for LKP_MIN_VALUE but I only want LKP_MIN_VALUE<=SRC_VALUE3
It is not possible to move the three Selects into one source select. The source and first lookup select are pretty big and the code for the transform would also need to be incorporated.
Using a Join stage joining on a dummy field then a filter to check the > and < works except there are 2.3 million records in the source and luckily only 83 in the lookup, but it still creates 197 million records to be filtered.
I could do the source and lookup1 in one job and write to a working table. Then join the working table to lookup2 but that would require a DBA and I am sure they would not be happy creating a working table for this issue since there may be many in the future.
What I find strange is this is really simple to do using a server job.
Does any one know some simple trick to do a lookup using >= without having to over generate records?