join with between condtion

[rgandra]

Hi Everybody,

Please help out with the following scenario.

I have a target fact table which contains about 30 millions of data which contains two main column tgt_month,Count.

Source file has the src_load_month and src_eff_month.

If the src_load_month > src_eff_month from the source file then
1)get the rows from target Fact table with the condition where tgt_month between src_load_month and src_eff_month
2)multiply the Count coloumn with -1 for each row comming out of POINT#1

[roy]

Hi,
Before we can answer can you please make clear, are you talking about a server or Parallel job?

[rgandra]

It should be parllel

[roy]

Hi,
(By the way you should have found lots of info on this if you searched the forum! so please do so next time)

Soon in the Hawk version there will (at least should) be a ranged lookup.

But for now you need to use work-arounds:
1. do the lookup directly against your DB and use user defined SQL to have the betwen clause you need.

2.Loading the data column to a temp table, then building a lookup file consisting of the actual value you have in your processed data and the values you need that you prepared from joining the previously loaded temp table and the lookup table you need to perform a between lookup with.

There are probably more methods out there, simply choose the one that suites you the most and gives you the best performance.

IHTH,