Daisy Chain Logic -Tuning

General

Daisy Chain Logic -Tuning

PFB the table(work_table) created and the data in it:

p_cust p_r_cust p_date
1 2 07/07/2011
2 3 07/09/2011
3 4 07/11/2011

update a
from work_table a ,work_table b
set p_r_cust = b.p_r_cust
where b.p_date >= a.p_date
and b.p_cust = a.p_r_cust
and b.p_r_cust <> a. p_r_cust;

Result table after the first run of the above mentioned query:

p_cust p_r_cust p_date
1 3 07/07/2011
2 4 07/09/2011
3 4 07/11/2011

Result table after the second run of the above mentioned query:

p_cust p_r_cust p_date
1 4 07/07/2011
2 4 07/09/2011
3 4 07/11/2011

DESIRED RESULT- After one run of the query :

p_cust p_r_cust p_date
1 4 07/07/2011
2 4 07/09/2011
3 4 07/11/2011

Can anyone suggest as how the query can be tuned such that above mentioned
result table is achieved in one run of the query and not two runs.

1 REPLY

Re: Daisy Chain Logic -Tuning

Too late to respond I guess, but the solution to this will involve a recursive query - that way you can execute only 1 sql but it will loop through X number of times and repeat the same function. You can define what X needs to be say 10 or 20 - whatever is a safe number of loops to assign the relations you need.