Lookup on Master table

[datamart]

I have a master table with following columns
Table1
-----------
Code
Type
LookupValue

Code and Type columns are primary keys.

The sample data for this table will look like this

Code Type LookupValue
-------------------------------------------------------
A all 1
B exclude X 2
C exclude Y 3
D include Z 4
B include X 5
C include Y 6
D exclude Z 7

This means that a CODE=A with all possible combinations of TYPE will result in a lookup value of 1.
Similarly, CODE=B and all values of TYPE other than X will result in a lookup value of 2
and CODE=B and all TYPE = X will result in a lookup value of 5.
And so on...

Note that, I do not have an exploded form of the master list. I receive the master list in the above format.

I will receive the fact data in a file in the following format

Code Type FactData
-------------------------------------
A X
A Y
B X
B Z

Please advise me as to how to select the correct lookup value from the master table while processing the fact file.

 

[blom0344]

Does the master-file contain just these 7 combinations?
Then you could do without the lookup and use a decode on the 10 (!) existing variations.

If not so , then you will need to create as many lookups as there are values for code (minus 1, cause value for A is fixed) and use SQL overrides with the proper SQL statements.

Ties Blom

 

[datamart]

HI..
there is no fixed list. what i gave was just an example.. all the 3 columns could be like the one i explained.. thats the reason why i have trouble finding a solution.