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.

 

[SantaMufasa]

Datamart, Here is some code that does what you ask:

col code format a4
col type format a4
select code, type, lookupvalue
  from (select f.code, f.type
              ,case when m.type = 'all' then m.lookupvalue
                    when 'include '||f.type = m.type then m.lookupvalue
                    when 'exclude '||f.type = m.type then null
                    when m.type like '%exclude%' then m.lookupvalue
                    else null
               end LookUpValue
          from mstr m, facts f
         where f.code = m.code)
 where lookupvalue is not null;

CODE TYPE LOOKUPVALUE
---- ---- -----------
A    X              1
A    Y              1
B    X              5
B    Y              2

Let us know if this is satisfactory.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[maswien]

how about this:

select f.code, f.type, f.FactData, m.lookupValue  
from
m, f
where m.code = f.code 
 and 
 (m.type= 'include ' || f.type 
  or m.type <> 'exclude ' || f.type 
  or m.type = 'all')

 

[SantaMufasa]

Maswien,

The problem with the code you posted is that rows that contain a TYPE that the master table does not deal with explicitly, end up with an extraneous row. Here are the results using your code:

select f.code, f.type, f.FactData, m.lookupValue
from
mstr m, facts f
where m.code = f.code
 and
 (m.type= 'include ' || f.type
  or m.type <> 'exclude ' || f.type
  or m.type = 'all');

CODE TYPE F LOOKUPVALUE
---- ---- - -----------
A    X                1
A    Y                1
B    X                5
B    Y                2
B    Y                5 <-- Extraneous result

Notice that the last row ("B, Y") has both a correct result and an extraneous result (per Datamart's rules).

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[maswien]

Thanks mufasa, I change it to following:

select f.code, f.type, f.FactData, m.lookupValue  
from
master_t m, fact_t f
where m.code = f.code 
 and 
 (m.type= 'include ' + f.type 
  or (m.type <> 'exclude ' + f.type) 
  and m.type like '%exclude%' 
  or m.type = 'all')

 

[SantaMufasa]

Maswien, if you replace the "+" operator with the "||" operator, then that seems to produce correct results. Your code is tighter than mine and a good improvement, IMHO.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[maswien]

I actually studied your code first and that's why it's tighter, the reason for '+' operator is that I use SQL Server database to test the code and forgot to change it to oracle '||', I found I can learn more oracle in this forum.

 

[datamart]

Thank you very much maswein and mufasa...
I was able to come out with a different solution for the same.
I used flags in the master table that indicates inclusion or exclusion. Depending on the flags I use Case statements for joining the columns correspondingly.
Actually, I wanted to solve this in an Informatica mapping. I converted my solution to corresponding table lookup transformation and achieved my requirement.
The other complexity was that, both the columns in the master table were variable..meaning.. the 'code' column could also have the include and exclude options.

Damn... the users always have to come out with complex requirements..

thanks again..