Converting Hierarchical Query from Oracle to MS SQL server 7

http://www.microsoft.com/resources/documentation/sql/7/all/proddocs/en-us/admincmp/75517c11.mspx

   DECODE seems to correspond to COALESCE
   [START WITH ... CONNECT BY]  seems to equate to a WHERE clause

-- Dan

Hi arunatata,

let's translate this part first :
Select ID from KUserChild start with Childid =2547 connect by Prior ParentID=ChildID

--since "connect by" does not exist in SQL server, i'd emulate this with a user-defined function
--this function retries chid records recursively

create function dbo.ufn_getChildUsers(@userID int)
returns @t table(userID int, lvl int)
as
begin
declare @lvl int
insert @t values(userID, 1)
while @@rowcount > 0
begin
      insert @t select a.ChildID, @lvl1+
      from KUserChild a
      inner join @t b on a.ParentID = b.ChildID and b.lvl = @lvl
      set @lvl = @lvl+1
end
return
end
go
-- to check that it works, try
select * from dbo.ufn_getChildUsers(2547) a


--how to use it then

Select G.ParentID, G.Name as Group, Isnull(P.Name, '') as Project
From KUser G
Left Outer Join Projects P On g.type=P.ProjID
where G.Type=1 or G.Type>=2000 and g.deleted=0
and G.ParentID in (select userID from dbo.ufn_getChildUsers(2547))

or (better IMHO using a join)

Select G.ParentID, G.Name as Group, Isnull(P.Name, '') as Project
From KUser G
Left Outer Join Projects P On g.type=P.ProjID
Inner join dbo.ufn_getChildUsers(2547) U on G.parentID = U.userID
where G.Type=1 or G.Type>=2000 and g.deleted=0

Note on logical operator precedence
AND as a higher precedence then OR in SQL Server, so

where G.Type=1 or G.Type>=2000 and g.deleted=0
is exactly the same than
where G.Type=1 or (G.Type>=2000 and g.deleted=0 )

if it's meant otherwise, you'll have to mak eit more explicit in your code using parens as follows
eg
where (G.Type=1 or G.Type>=2000) and g.deleted=0

Hi Hilaire,

Thanks for the response. I have one question - what does the variable lvl do here?

Also it gives me the error "The name UserId is not permitted in this context. Only constants, expressions, or variables are allowed here. Column names are not permitted".

there was a missing "@" in the first insert and another typo in the second insert

drop function dbo.ufn_getChildUsers
go
create function dbo.ufn_getChildUsers(@userID int)
returns @t table(userID int, lvl int)
as
begin
declare @lvl int
insert @t values(@userID, 1)
while @@rowcount > 0
begin
     insert @t select a.ChildID, @lvl + 1
     from KUserChild a
     inner join @t b on a.ParentID = b.ChildID and b.lvl = @lvl
     set @lvl = @lvl+1
end
return
end
go


as for the @lvl variable,
it's necessary to now the "level of recursion", of, if your prefer, how deep you are in the parent-child relationship.

after the first insert, the level is 1
at the second insert in the returned table, we insert all child records whose father has a level of 1
at the third insert, we insert all child records whose father has a level of 2

this part of the join
" and b.lvl = @lvl"
is to avoid inserting records already inserted in the previous loop.

HTH
Hilaire

Hi Hilaire,

That definitely helped. Thanks for the explanation. However when I run the UDF its only returning one level - the very first thats inserted. What could be going wrong here?

Thanks,
A.

Please post table definition fo the KUserChild table

I'm a bit confused on the relationships
your code has fields ID, ChildUID, ParentID

I kept only childID and parentID, so I might have a wrong join in the 2nd insert  

Hilaire

My mistake - it was a typo in the earlier post - my table has only the fields ParentID and ChildID. There is no third field. The table fields are ParentID, ChildID - both int 4.

Thanks Hilaire - that worked!!I appreciate all the help you gave me on this query.

Regards,
A.