All Forums General
khushbu.bharat 1 post Joined 08/11
15 Aug 2011
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.

vickyejain 11 posts Joined 02/10
17 Jun 2012

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.

You must sign in to leave a comment.