How to make a recursive Function in sql server..

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to make a recursive Function in sql server..

Author

Topic

tariq.vm
Starting Member

3 Posts

Posted - 2009-10-22 : 05:32:19

I am getting problem in make a function for the following situation.

Suppose i have a table named Opportunity_Duplicate_Tbl

Opp_ID Dup_ID
==================
R1 111 200
R2 111 300
R3 111 400
R4 222 56
R5 300 200
R6 101 400
R7 112 50
R8 100 101

where R1, R2.... indicates Row number and not in table.
Here we have two cols Opp_ID and Dup_ID.
Here 200,300,400 are duplicates of 111. Total Duplicates of 111 are 3 right now.
But 200 and 400 are duplicates of 300 and 101 respectively.
Think relation like a=b and b=c, then c=a or a=c also.
So total duplicates of 111 are 5.As 101 is duplicate of 100 and 400 is duplicate of 101.. we have ommited the R5 row since 200 and 300 are direct duplicates of 111.

So 200,300,400,101 and 100 are total duplicates of 111.
I have solve the problem of getting total number of "Dup_ID" for each "Opp_ID".Please help me out.
Thanks in advance.

In a day when don't come across any problem - you can be sure that you are traveling in wrong path.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-10-22 : 05:34:42

You can use a recursive CTE to solve this problem, if you are working with Microsoft SQL Server 2005 or later.

N 56°04'39.26"
E 12°55'05.63"

tariq.vm
Starting Member

3 Posts

Posted - 2009-10-22 : 06:00:22

quote:
Originally posted by Peso

You can use a recursive CTE to solve this problem, if you are working with Microsoft SQL Server 2005 or later.

N 56°04'39.26"
E 12°55'05.63"

In a day when don't come across any problem - you can be sure that you are traveling in wrong path.

tariq.vm
Starting Member

3 Posts

Posted - 2009-10-22 : 06:01:33

well i am new to programming... and atcually never made any sql server function before... only wrote some simple queries. Could please send me some code regarding this.. i will be highly thankfull of you...
Please help....

In a day when don't come across any problem - you can be sure that you are traveling in wrong path.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources