vendredi 17 juin 2016

sql recursive cte in view not work fine

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