I made a CTE query that will works fine in a stored procedure and when i execute in query-editor (With where statement)
But when i try it out to create the same as View (Without where statement). And execute this with the where statement i get the data not recursive. I dont see the mistake.
This is the Query in SP and query-editor which works fine. (Guid in SP is as parameter)
WITH recursiveTable(ParentApplicationRoleId, ChildApplicationRoleId)
AS
(
SELECT ParentApplicationRoleId, ChildApplicationRoleId
FROM ApplicationRoleParentChild
WHERE ParentApplicationRoleId = '69BCC94B-2884-4270-B39E-A09669C1D658'
UNION ALL
SELECT ApplicationRoleParentChild.ParentApplicationRoleId, ApplicationRoleParentChild.ChildApplicationRoleId
FROM ApplicationRoleParentChild
INNER JOIN recursiveTable ON ApplicationRoleParentChild.ParentApplicationRoleId = recursiveTable.ChildApplicationRoleId
)
/* Get all Childelements (from recursive Table) and add the ParentRoleId to this List */
SELECT ApplicationRole.Name AS RoleName
FROM recursiveTable
INNER JOIN ApplicationRole ON ApplicationRole.ApplicationRoleId = recursiveTable.ChildApplicationRoleId
UNION
SELECT Name AS RoleName FROM ApplicationRole WHERE ApplicationRoleId = '69BCC94B-2884-4270-B39E-A09669C1D658'
OPTION(MAXRECURSION 32767)
As Result i get Result Image
The View need all ApplicationRoles (with all Childroles) and unfiltered, because i'll use the filter when i call the View.
How i can change the CTE that it works without where statement?
I have this as example. But it returns only the direct Childelements of 'Administrator' And i search Child from the Childs, too.
WITH recursiveTable(ParentApplicationRoleId, ChildApplicationRoleId)
AS
(
SELECT ParentApplicationRoleId, ChildApplicationRoleId
FROM ApplicationRoleParentChild
UNION ALL
SELECT ApplicationRoleParentChild.ParentApplicationRoleId, ApplicationRoleParentChild.ChildApplicationRoleId
FROM ApplicationRoleParentChild
INNER JOIN recursiveTable ON ApplicationRoleParentChild.ParentApplicationRoleId = recursiveTable.ChildApplicationRoleId
)
/* Get all Childelements (from recursive Table) and add the ParentRoleId to this List */
SELECT ApplicationRole.Name AS ChildRoleName, Parent.Name AS ParentRoleName
FROM recursiveTable
INNER JOIN ApplicationRole ON ApplicationRole.ApplicationRoleId = recursiveTable.ChildApplicationRoleId
INNER JOIN ApplicationRole as Parent ON Parent.ApplicationRoleId = recursiveTable.ParentApplicationRoleId
/*The Query-Filter when i call the View: Like SELECT * FROM View WHERE Parent.Name = 'Administrator'
Expected Returns: Role1-10, Departement1-3 and Administrator
Current Returns: Only direct Childs (Role2,7,8, Departement1,3)
Departement2 is Child of Departement1
And some other Roles are in one of the Departements or roles
*/
Tables: ApplicationRole (all Roles) ApplicationRoleParentChild (All Parent Child Relationships) (With FK Parent and FK Child Role)
I Hope someone can help me.
Thanks
Aucun commentaire:
Enregistrer un commentaire