recursive function

[Microsoft]

I've a table of users like this

id_user id_ref flg_enabled
1 0 1
2 1 1
3 1 0
4 2 1

id_user 1 created id_user 2 and 3
id_user 2 created id_user 4

This rule can by infinite

I need a recursive function that tells me when a user and all his "fathers"
are enabled
If one of its father is disabled (flg_enabled=0) the function have to return
0
If all the father (till id_ref=0) are enabled (flg_enabled=1) the function
have to return 1

function is_enabled(id_user)
.....
End Function

Can you please help me?

Thanks

 

[Dave Anderson]

I would think one of your many employees could do this for you, Microsoft.

I've a table of users like this

id_user id_ref flg_enabled
1 0 1
2 1 1
3 1 0
4 2 1

id_user 1 created id_user 2 and 3
id_user 2 created id_user 4

This rule can by infinite

I need a recursive function that tells me when a user and all his
"fathers" are enabled
If one of its father is disabled (flg_enabled=0) the function have to
return 0
If all the father (till id_ref=0) are enabled (flg_enabled=1) the
function have to return 1

function is_enabled(id_user)
....
End Function

--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.

 

[Microsoft]

I think I've some missing configuration in my outlook express, that's why
you see "Microsoft"

 

[Dave Anderson]

I've a table of users like this

id_user id_ref flg_enabled
1 0 1
2 1 1
3 1 0
4 2 1

id_user 1 created id_user 2 and 3
id_user 2 created id_user 4

This rule can by infinite

I need a recursive function that tells me when a user and all his
"fathers" are enabled
If one of its father is disabled (flg_enabled=0) the function have to
return 0
If all the father (till id_ref=0) are enabled (flg_enabled=1) the
function have to return 1

function is_enabled(id_user)
....
End Function

If you are using MS SQL Server, you can use a user-defined function:

CREATE FUNCTION dbo.is_enabled(
@ID INT
) RETURNS BIT AS
BEGIN
DECLARE @Product INT, @Parent INT

SELECT @Product = flg_enabled,
@Parent = id_ref
FROM YourTable
WHERE id_user = @ID

IF @Parent > 0 SET @Product = @Product * dbo.is_enabled(@Parent)
RETURN @Product
END

Test your results:

SELECT *, dbo.is_enabled(id_user) AS is_enabled FROM YourTable

Results:

id_user id_ref flg_enabled is_enabled
1 0 1 1
2 1 1 1
3 1 0 0
4 2 1 1
5 3 1 0
6 2 0 0
7 4 1 1


Be aware that SQL Server "only" allows 32 levels of recursion, so if an
element has 32 "fathers", this will fail.



--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.