lundi 11 juillet 2016

Using a recursive CTE in a view

Having a typical parent/child hierarchy table it's a common thing to query it using Common Table Expression:

 with CTE  as (
    select Id, ProviderId, ConsumerId
    from T1
    where ProviderId in (2, 3, 9)

    union all
    select T1.Id, T1.ProviderId, T1.ConsumerId
    from T1  
    join CTE on C.ProviderId = CTE.ConsumerId 
)
select * from CTE

Is it possible to create a view based on this query so that one can do:

select * from MagicView where ProviderId in (2,3,9)

In other words, can we somehow extract parameters from the anchor part of the CTE to create a generic view?

Aucun commentaire:

Enregistrer un commentaire