Select Distinct on a join?

Question

Hi,

Here is the problem.

Fact Table - X contains dollar amounts, it joins to Dim - X table by RowID = RowID. In certain instances, the Dim - X table repeats the same RowID, because some of the other columns in Dim - X are different. In these certain instances, the dollar amounts from Fact - X are multiplied buy the number of repeated RowIDs in Dim - X.

Is there any way to work around this by putting a distinct on the Fact - X. Dollar amount column or somehow in the dimension.

Thanks

SriniVEERAVALLI · Answer

If it is custom mapping then I would suggest to check group by clause or agg trans in your mapping.

for any assistance : expect more detail query.

3173736 · Answer

could you possibly use a subquery?

where your join condition would look like:

inner join (select distinct f1,f2,f3... from secondTable) t2

on fact.rowid=t2.rowid