August 3, 2010 at 10:20 am
i have a recursive CTE that steps down through a table and returns a tree structure based on a param for a field. I have enclosed this in a table function.
select * from Relationship('joe smith') returns the tree
starting withj joe smith. I need to join this table def to
other tables within a view.
create view myview as
select * from table1 join Relationship on ......
join table2 on .....
so my user can just do
select * from myview
where table1.field_name='Joe Smith')....
such that 'joe smith' is passed into the recursive CTE
for the anchor query
I know there is no way to pass the 'joe smith' into the view then into the CTE. I can not use a stored proc (do not ask)
I am unable to find a method such that I can do a query to get a result set that I then can join to my tabledef or cte to get the hierarchy.
I want to remove the @name in the cte and have party_name=...
I would think this is a common type operation but I can not find
any examples of a solution
create function Relationship(@name varchar(100))
returns table
as return
(
WITH PartyCTE (Party_Key, Parent_Key, Parent_role_type,Party_role_type,Level )
AS
(
-- Anchor member definition
SELECT e.Party_Key, e.Parent_Key,e.Parent_role_type,e.Party_role_type,
0 AS Level
from Party_Relationship e join dim_Party p on e.Party_Key=p.Party_Key
WHERE p.Party_Name=@name
UNION ALL
-- Recursive member definition
SELECT e1.Party_Key,e1.Parent_Key,e1.Parent_role_type,e1.Party_role_type,
Level + 1
FROM Party_Relationship e1 join PartyCTE d
on d.Party_Key=e1.Parent_Key
)
select c.Party_Key,p.Party_Name,c.Party_Role_Type,isnull(p2.Party_Name,0)as Parent,c.Parent_Role_type ,Level
from PartyCTE c join dim_Party p on p.Party_Key=c.Party_Key
left outer join dim_Party p2 on p2.Party_Key=c.Parent_Key
)
August 26, 2010 at 11:32 pm
You said you could not use a stored procedure, how about an inline table valued function? Then you could pass in a value to a variable and use like a view.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply