fan Trap

building semantic layer
1

Hello All,

I have two detail tables. Following is the sql for both the tables:

T1:

select m.VEHICLE_NO,m.PRODUCT_CODE,m.FEE_AMT,m.TAX_AMT
from fee_matrix m
where lessee_code=‘0009’
and yr_mon=200910
and vehicle_no=‘26025’

Results:

vehicle # Prod. Code fee_amt tax
26025 007 4.00 0.00
26025 DPR 264.68 0.00
26025 ADM 6.17 0.00
26025 INT 0.76 0.00
26025 022 4.50 0.00
26025 PA 8.15 0.00
26025 TAX 0.00 16.81

T2:

select m.VEHICLE_NO,m.INVOICE_NO,m.INVOICE_DATE,m.BILLED
from b_invoice m
where lessee_code=‘0009’
and yr_mon=200910
and vehicle_no=‘26025’

Results:

Veh # Invoice # Inv. date Billed
26025 B66006 9/26/2009 296.92
26025 B66006 9/26/2009 8.15

Table T1 is the detail description of invoices from T1. The amount billed in T2 is equal to fee_amt + tax.

I have to join these tables to put product code,invoice #, and invoice Date.
The tables are joined by lessee_code, vehicle_no, and yr_mon.

I have read all the articles on the website and did accordingly but I still get a fan trap when I combine the two. Can someone help in resolving the issue?

Thanks,
Purnima Sharma

prnmsharma :us: (BOB member since 2008-03-18)

2

…which is not a unique combination, so that results in a many-to-many join. You need to find a composite primary key if not a primary key in either of the tables, make sure the corresponding column(s) are present in the other table and then apply the join based on these.

vinod_menon :india: (BOB member since 2007-04-09)