Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>After the <code>WITH</code> has been specified (beyond the closing parenthesis), you need to select all values from the CTE:</p> <pre><code>select * from xxDirectReports </code></pre> <p>This is the actual select query that constitutes the view.</p> <p>This is the complete working example, with selecting the final output of the view, and some additional <code>GO</code> statements to allow the statements to be run in a single execution within SQL Server Management Studio:</p> <pre><code>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; </code></pre> <p><strong>Update:</strong></p> <blockquote> <p>Also, is it possible to fetch the record that starts with , based on sample data, LFG and not with SMY?</p> </blockquote> <p>Yes! Although the approach varies. <em>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</em>. 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 <strong>Hierarchical Path</strong>.</p> <p>To add this behaviour, add a <code>Path</code> column name to the <code>WITH</code> clause, and add the following, firstly in the anchor statement:</p> <pre><code>convert(nvarchar(256), RTRIM(convert(nvarchar(12), hre.PERSON_ID))) Path </code></pre> <p>And secondly, in the recursive statement:</p> <pre><code>convert(nvarchar(256), rtrim(Path) + '.' + RTRIM(convert(nvarchar(12), hre.PERSON_ID))) Path </code></pre> <p>Then, to select the hierarchy root (LFG) and all subordinates, simply modify the query that selects from the view to this:</p> <pre><code>select * from HR_DIRECTREPORTSV where Path = '3' or Path like '3.%' </code></pre> <p>The results are as follows:</p> <pre><code>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 </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POCreate view based on hierarchy / used of CTE
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USRabid
    1. USRabid
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POCreate view based on hierarchy / used of CTE
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COHi 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.
      singulars
      1. PO
      1. USElmer
    2. COThis 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.
      singulars
      1. PO
      1. USRabid
    3. COHello Rabid. That's great. I was thinking of revisiting my inner join statement of put a distinct clause. Many thanks. Regards,
      singulars
      1. PO
      1. USElmer
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload