ansaurus

Question

Create view based on hierarchy / used of CTE

Answer 1

+1 A:

Yes you can - something like

CREATE VIEW [mySchema].vwItem
AS
    WITH cteItem     
    AS     
    (    
        SELECT      
        item_id     AS 'ItemID',    
        item_level    AS 'ItemLevel',     
        item_number_type  AS 'ItemNumberType',    
        CASE item_level     
            WHEN 1 THEN item_id     
            WHEN 2 THEN item_parent_id     
            WHEN 3 THEN item_grandparent_id
        ELSE item_id     
        END      AS 'ItemParent',    
        FROM     
        [mySchema].[ITEM]
    )    

    SELECT     
        child.*,    
        parent.item_id   AS 'ParentId',    
        parent.desc  AS 'ParentDescription'    
    FROM     
        cteItem child  
    LEFT OUTER JOIN [mySchema].[ITEM] parent ON     
        child.ItemParent = parent.item_id

nonnb 2010-08-04 07:03:01

Answer 2

+2 A:

After the WITH has been specified (beyond the closing parenthesis), you need to select all values from the CTE:

select * from xxDirectReports

This is the actual select query that constitutes the view.

This is the complete working example, with selecting the final output of the view, and some additional GO statements to allow the statements to be run in a single execution within SQL Server Management Studio:

if not OBJECT_ID('XHR_PERSON', 'Table') is null drop table XHR_PERSON
if not OBJECT_ID('XHR_EMPLOYMENT', 'Table') is null drop table XHR_EMPLOYMENT
if not OBJECT_ID('HR_DIRECTREPORTSV', 'View') is null drop view HR_DIRECTREPORTSV

CREATE TABLE [dbo].[XHR_PERSON](
[PERSON_ID] [bigint] NOT NULL,
[LAST_NAME] [varchar](100) NOT NULL,
[FIRST_NAME] [varchar](100)
,EFFECTIVE_START_DATE Date
,EFFECTIVE_END_DATE Date)

CREATE TABLE [dbo].[XHR_EMPLOYMENT](
[PERSON_ID] [bigint] NOT NULL,
[EMPLOYEE_NUMBER] [varchar](100) NULL,
[SUPERVISOR_ID] [bigint] NULL
,EFFECTIVE_START_DATE Date
,EFFECTIVE_END_DATE Date)

insert into XHR_PERSON 
select 1, 'SMY', null, '1990-01-01','9999-12-31' UNION ALL 
select 2, 'JSB',null, '1990-01-01','9999-12-31' union all
select 3, 'LFG',null, '1990-01-01','9999-12-31' union all
select 4, 'Elmer',null, '1990-01-01','9999-12-31' union all
select 5, 'Jon',null, '1990-01-01','9999-12-31' union all
select 6, 'Anne',null, '1990-01-01','9999-12-31' union all
select 7, 'Teddy',null, '1990-01-01','9999-12-31' union all
select 8, 'Alex',null , '1990-01-01','9999-12-31'union all
select 9, 'Jeff',null, '1990-01-01','9999-12-31'
update XHR_PERSON set first_name = 'A'

insert into XHR_EMPLOYMENT
select 1, '111',null, '1990-01-01','9999-12-31' UNION ALL 
select 2, '222',1, '1990-01-01','9999-12-31' union all
select 3, '333',1, '1990-01-01','9999-12-31' union all
select 4, '444',2, '1990-01-01','9999-12-31' union all
select 5, '555',2, '1990-01-01','9999-12-31' union all
select 6, '666',4, '1990-01-01','9999-12-31' union all
select 7, '777',3, '1990-01-01','9999-12-31' union all
select 8, '888',3, '1990-01-01','9999-12-31' union all
select 9, '999',8, '1990-01-01','9999-12-31';

GO
CREATE VIEW dbo.HR_DIRECTREPORTSV as
WITH xxDirectReports (Supervisor_id, Person_id, Employee_number, Employee_name, Supervisor_Empno, Supervisor_Name, Level1)
AS
(
SELECT hre.Supervisor_id
    ,hre.Person_id
    ,hre.Employee_number
    ,hrp.last_name+', '+hrp.first_name  Employee_Name
    ,hrpx.employee_number       Supervisor_Empno
    ,hrpx.fullname          Supervisor_Name
    ,0 AS Level1
FROM dbo.xhr_employment AS hre left join (select hrp1.person_id,hre1.employee_number                                                    ,(hrp1.last_name+', '+hrp1.first_name) as fullname
                                           from dbo.xHR_PERSON hrp1
                                                ,dbo.xhr_employment hre1
                                           where hrp1.person_id = hre1.person_id
                                           AND   getdate() between hrp1.effective_start_date 
                                                            and    hrp1.effective_end_date
                                        ) hrpx on hre.supervisor_id = hrpx.person_id
    ,dbo.xHR_PERSON     AS hrp 
WHERE hre.person_id = hrp.person_id 
AND   GETDATE() between hrp.effective_start_date and hrp.effective_end_date
AND   GETDATE() between hrp.effective_start_date and hrp.effective_end_date
--AND   hrpx.person_id = 1
UNION ALL
SELECT   hre.Supervisor_id
        ,hre.Person_id
        ,hre.Employee_number
        ,hrp.last_name+', '+hrp.first_name  Employee_Name
        ,hrpx.employee_number               Supervisor_Empno
        ,hrpx.fullname                      Supervisor_Name
        ,Level1+1
FROM dbo.xhr_employment AS hre inner join (select hrp1.person_id    
                                                ,hre1.employee_number
                                                ,(hrp1.last_name+', '+hrp1.first_name) as fullname
                                           from dbo.xHR_PERSON hrp1
                                                ,dbo.xhr_employment hre1
                                           where hrp1.person_id = hre1.person_id
                                           AND   getdate() between hrp1.effective_start_date 
                                                            and    hrp1.effective_end_date
                                        ) hrpx on hre.supervisor_id = hrpx.person_id
                                INNER JOIN xxDirectReports AS xx ON hre.Supervisor_id = xx.Person_id
    ,dbo.xHR_PERSON     AS hrp 

WHERE hre.person_id = hrp.person_id 
AND   GETDATE() between hrp.effective_start_date and hrp.effective_end_date
AND   GETDATE() between hrp.effective_start_date and hrp.effective_end_date)
select * from xxDirectReports;

GO
select * from HR_DIRECTREPORTSV;

Update:

Also, is it possible to fetch the record that starts with , based on sample data, LFG and not with SMY?

Yes! Although the approach varies. I would recommend having a variable within the CTE's anchoring statement, and instead of having a view, create it as a table valued function. However, there are a number of different ways to go about selecting hierarchical data once it's assumed hierarchical form ... one of the approaches is a Hierarchical Path.

To add this behaviour, add a Path column name to the WITH clause, and add the following, firstly in the anchor statement:

convert(nvarchar(256), RTRIM(convert(nvarchar(12), hre.PERSON_ID))) Path

And secondly, in the recursive statement:

convert(nvarchar(256), rtrim(Path) + '.' + RTRIM(convert(nvarchar(12), hre.PERSON_ID))) Path

Then, to select the hierarchy root (LFG) and all subordinates, simply modify the query that selects from the view to this:

select * from HR_DIRECTREPORTSV
where Path = '3' or Path like '3.%'

The results are as follows:

Path   Supervisor_id   Person_id   Employee_number   Employee_name   Supervisor_Empno   Supervisor_Name   Level1
3   1   3   333 LFG, A  111 SMY, A  0
3.7 3   7   777 Teddy, A    333 LFG, A  1
3.8 3   8   888 Alex, A 333 LFG, A  1
3.8.9   8   9   999 Jeff, A 888 Alex, A 2

Rabid 2010-08-04 10:22:47

Hi Rabid, Thanks for this one. It's the select * from xxDirectReports that creates the view. And also for the "path" you specified. That worked pretty well. I have another problem though, as there are duplicate rows in the output. I guess I just need to revisit my join statements.Thanks. Appreciate all the help.

Elmer 2010-08-04 10:58:21

This is because your anchoring query selects all records, instead of root ones. If you wish to fix this, simply `AND` this expression: `SUPERVISOR_ID is null` into your anchor query `WHERE` clause. You'll have to modify your `Path` expression to `like '%.3.%'` to compensate for the newly restricted result set.

Rabid 2010-08-04 11:09:56

Hello Rabid.That's great. I was thinking of revisiting my inner join statement of put a distinct clause. Many thanks. Regards,

Elmer 2010-08-05 02:35:01

ansaurus

tags:

views:

answers:

Create view based on hierarchy / used of CTE

related questions