November 10, 2008 at 10:08 am
Hi Experts,
I have developed a web based software for a Multi level Marketing Company where one member joins and then gets 2 members below him - one on the left and other the right leg.
For getting count for any member I have a stored procedure which calls user defined function. The user defined function calls the same function for getting the leg count of any member who logs in and wishes to see leg count.
Every thing works fine till a member reaches 950 members below him but after more than 950 members get registered below a member (on either leg) the nesting error is shown when a member clicks on one's leg count link provided to see leg count.
Being a newbie I request clear guidance as to how to resolve this since I have provide solution to client tomorrow.
Stored procedure is as follows:
CREATE Procedure GetLegCount_forBSGUser
@user-id int
AS
select count(*) as count from BSGGetReports3(1,@UserID) where status='Y'
GO
User defined function is:
CREATE FUNCTION dbo.BSGGetReports3(@IncludeParent bit, @user-id int)
RETURNS @retFindReports TABLE (UserID int, Name varchar(50), ReferID int, LegType varchar(1), Status varchar(1))
AS
BEGIN
IF (@IncludeParent=1)
BEGIN
INSERT INTO @retFindReports
SELECT userid,username,referid,PositionType,Status FROM BSGUserregister WHERE userID=@UserID
END
DECLARE @Report_ID int, @Report_Name varchar(50), @Report_BossID int, @Report_LegType varchar(1), @Report_Status varchar(1)
DECLARE RetrieveReports CURSOR STATIC LOCAL FOR
SELECT userid,username,referid,PositionType, Status FROM BSGUserRegister WHERE referID=@UserID union
SELECT userid,username,referid,PositionType, Status FROM BSGUserRegister WHERE parentID=@UserID
OPEN RetrieveReports
FETCH NEXT FROM RetrieveReports
INTO @Report_ID, @Report_Name, @Report_BossID,@Report_LegType, @Report_Status
WHILE (@@FETCH_STATUS = 0)
BEGIN
INSERT INTO @retFindReports
SELECT * FROM dbo.BSGGetReports3(0,@Report_ID)
INSERT INTO @retFindReports
VALUES(@Report_ID,@Report_Name, @Report_BossID, @Report_LegType,@Report_Status)
FETCH NEXT FROM RetrieveReports
INTO @Report_ID, @Report_Name, @Report_BossID, @Report_LegType,@Report_Status
END
CLOSE RetrieveReports
DEALLOCATE RetrieveReports
RETURN
END
DDL for the data and sample data is:
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#BSGUserregister','U') IS NOT NULL
DROP TABLE #BSGUserregister
--===== Create the test table with
CREATE TABLE #BSGUserregister
(
USERID INT,
UserName varchar(50),
ReferId INT,
PositionType varchar
[Wink]
INSERT INTO #BSGUserregister (USERID ,UserName ,ReferId ,PositionType )
SELECT '9999','RRS','0','M'
SELECT '10000','prs1','9999','L'
SELECT '10001','prs2','9999','R'
SELECT '10002','rvs1','10000','L'
SELECT '10003','rvs2','10000','R'
SELECT '10004','rvs3','10001','L'
SELECT '10005','rrs2','10001','R'
SELECT '10006','rrs2','10002','L'
SELECT '10007','rrs2','10002','R'
SELECT '10008','rrs2','10003','R'
Thanks in advance for all who send in their guidance
November 10, 2008 at 8:52 pm
ajaykini (11/10/2008)
Hi Experts,I have developed a web based software for a Multi level Marketing Company where one member joins and then gets 2 members below him - one on the left and other the right leg.
Heh... nothing quite like an illegal binary system... I thought they were outlawed an eon or two ago.
Anyway, the max recursion you can get out of a function in SQL Server 2000 is 32...
--Jeff Moden
Change is inevitable... Change for the better is not.
November 10, 2008 at 9:35 pm
ajaykini (11/10/2008)
Hi Experts,I have developed a web based software for a Multi level Marketing Company where one member joins and then gets 2 members below him - one on the left and other the right leg.
For getting count for any member I have a stored procedure which calls user defined function. The user defined function calls the same function for getting the leg count of any member who logs in and wishes to see leg count.
Every thing works fine till a member reaches 950 members below him but after more than 950 members get registered below a member (on either leg) the nesting error is shown when a member clicks on one's leg count link provided to see leg count.
Being a newbie I request clear guidance as to how to resolve this since I have provide solution to client tomorrow.
Stored procedure is as follows:
CREATE Procedure GetLegCount_forBSGUser
@user-id int
AS
select count(*) as count from BSGGetReports3(1,@UserID) where status='Y'
GO
User defined function is:
CREATE FUNCTION dbo.BSGGetReports3(@IncludeParent bit, @user-id int)
RETURNS @retFindReports TABLE (UserID int, Name varchar(50), ReferID int, LegType varchar(1), Status varchar(1))
AS
BEGIN
IF (@IncludeParent=1)
BEGIN
INSERT INTO @retFindReports
SELECT userid,username,referid,PositionType,Status FROM BSGUserregister WHERE userID=@UserID
END
DECLARE @Report_ID int, @Report_Name varchar(50), @Report_BossID int, @Report_LegType varchar(1), @Report_Status varchar(1)
DECLARE RetrieveReports CURSOR STATIC LOCAL FOR
SELECT userid,username,referid,PositionType, Status FROM BSGUserRegister WHERE referID=@UserID union
SELECT userid,username,referid,PositionType, Status FROM BSGUserRegister WHERE parentID=@UserID
OPEN RetrieveReports
FETCH NEXT FROM RetrieveReports
INTO @Report_ID, @Report_Name, @Report_BossID,@Report_LegType, @Report_Status
WHILE (@@FETCH_STATUS = 0)
BEGIN
INSERT INTO @retFindReports
SELECT * FROM dbo.BSGGetReports3(0,@Report_ID)
INSERT INTO @retFindReports
VALUES(@Report_ID,@Report_Name, @Report_BossID, @Report_LegType,@Report_Status)
FETCH NEXT FROM RetrieveReports
INTO @Report_ID, @Report_Name, @Report_BossID, @Report_LegType,@Report_Status
END
CLOSE RetrieveReports
DEALLOCATE RetrieveReports
RETURN
END
DDL for the data and sample data is:
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#BSGUserregister','U') IS NOT NULL
DROP TABLE #BSGUserregister
--===== Create the test table with
CREATE TABLE #BSGUserregister
(
USERID INT,
UserName varchar(50),
ReferId INT,
PositionType varchar
[Wink]
INSERT INTO #BSGUserregister (USERID ,UserName ,ReferId ,PositionType )
SELECT '9999','RRS','0','M'
SELECT '10000','prs1','9999','L'
SELECT '10001','prs2','9999','R'
SELECT '10002','rvs1','10000','L'
SELECT '10003','rvs2','10000','R'
SELECT '10004','rvs3','10001','L'
SELECT '10005','rrs2','10001','R'
SELECT '10006','rrs2','10002','L'
SELECT '10007','rrs2','10002','R'
SELECT '10008','rrs2','10003','R'
Thanks in advance for all who send in their guidance
I have worked on the same software as you are talking about. i did the same in stored procedure but it was hard to modify the logic. ad the complete tree base is on the member count on the left and right nodes, i found it is quite difficult to change the logic each and every time.
then i frame a function in visual basic, that counts the parent-childs and based on the count, it proceeds. if you want i can forward that function on your email id.
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
November 10, 2008 at 11:08 pm
Seems to me that a stored procedure, a temp table and a variation on the running count would give you what you need.
What do you think, Jeff?
November 10, 2008 at 11:11 pm
ajaykini (11/10/2008)
Hi Experts,I have developed a web based software for a Multi level Marketing Company where one member joins and then gets 2 members below him - one on the left and other the right leg.
For getting count for any member I have a stored procedure which calls user defined function. The user defined function calls the same function for getting the leg count of any member who logs in and wishes to see leg count.
Every thing works fine till a member reaches 950 members below him but after more than 950 members get registered below a member (on either leg) the nesting error is shown when a member clicks on one's leg count link provided to see leg count.
Being a newbie I request clear guidance as to how to resolve this since I have provide solution to client tomorrow.
Stored procedure is as follows:
CREATE Procedure GetLegCount_forBSGUser
@user-id int
AS
select count(*) as count from BSGGetReports3(1,@UserID) where status='Y'
GO
User defined function is:
CREATE FUNCTION dbo.BSGGetReports3(@IncludeParent bit, @user-id int)
RETURNS @retFindReports TABLE (UserID int, Name varchar(50), ReferID int, LegType varchar(1), Status varchar(1))
AS
BEGIN
IF (@IncludeParent=1)
BEGIN
INSERT INTO @retFindReports
SELECT userid,username,referid,PositionType,Status FROM BSGUserregister WHERE userID=@UserID
END
DECLARE @Report_ID int, @Report_Name varchar(50), @Report_BossID int, @Report_LegType varchar(1), @Report_Status varchar(1)
DECLARE RetrieveReports CURSOR STATIC LOCAL FOR
SELECT userid,username,referid,PositionType, Status FROM BSGUserRegister WHERE referID=@UserID union
SELECT userid,username,referid,PositionType, Status FROM BSGUserRegister WHERE parentID=@UserID
OPEN RetrieveReports
FETCH NEXT FROM RetrieveReports
INTO @Report_ID, @Report_Name, @Report_BossID,@Report_LegType, @Report_Status
WHILE (@@FETCH_STATUS = 0)
BEGIN
INSERT INTO @retFindReports
SELECT * FROM dbo.BSGGetReports3(0,@Report_ID)
INSERT INTO @retFindReports
VALUES(@Report_ID,@Report_Name, @Report_BossID, @Report_LegType,@Report_Status)
FETCH NEXT FROM RetrieveReports
INTO @Report_ID, @Report_Name, @Report_BossID, @Report_LegType,@Report_Status
END
CLOSE RetrieveReports
DEALLOCATE RetrieveReports
RETURN
END
DDL for the data and sample data is:
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#BSGUserregister','U') IS NOT NULL
DROP TABLE #BSGUserregister
--===== Create the test table with
CREATE TABLE #BSGUserregister
(
USERID INT,
UserName varchar(50),
ReferId INT,
PositionType varchar
[Wink]
INSERT INTO #BSGUserregister (USERID ,UserName ,ReferId ,PositionType )
SELECT '9999','RRS','0','M'
SELECT '10000','prs1','9999','L'
SELECT '10001','prs2','9999','R'
SELECT '10002','rvs1','10000','L'
SELECT '10003','rvs2','10000','R'
SELECT '10004','rvs3','10001','L'
SELECT '10005','rrs2','10001','R'
SELECT '10006','rrs2','10002','L'
SELECT '10007','rrs2','10002','R'
SELECT '10008','rrs2','10003','R'
Thanks in advance for all who send in their guidance
Based on your sample data, what is the expect results?
November 11, 2008 at 5:39 am
krayknot (11/10/2008)
if you want i can forward that function on your email id.
Heh... post it here so we can all enjoy it. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
November 11, 2008 at 5:58 am
Lynn Pettis (11/10/2008)
Seems to me that a stored procedure, a temp table and a variation on the running count would give you what you need.What do you think, Jeff?
The data example would make you think so because it's all nice and in order by UserID. But, in the real world, hierarchies just don't work that way... I believe the best thing to do would be to run Celko's "nested set" model in parallel with this "adjacency model".
http://www.developersdex.com/gurus/articles/112.asp?Page=1
I say to run it in parallel because the adjacency model is easier for people to understand and update... hierarchies don't change every second especially in an MLM... people have to be "signed up", etc, using other procs. Once that is done, then code to build the nested set from the adjacency model would be executed and it doesn't take long. The adjacency is easier for most people to maintain and the nested set model gives you the ability to do things like counts and upline/downline calculations in the proverbial blink of an eye.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 11, 2008 at 8:44 am
Kshitij:
That would be great & very nice of you.
I look forward to your function & hope that solves my problem too.
Ajay Kini
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply