Extend hierarchy select statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Extend hierarchy select statement

Author

Topic

fwinter2102
Starting Member

1 Post

Posted - 2014-01-27 : 07:59:59

Hi @all,

I've got a table/view which contains the organizational Units structure of the Active Directory.

With the following T-SQL Statement I get the hierarchy which works fine:


WITH cte AS (
 SELECT
  OU_GUID
 , OU_Name
 , OU_ParentGUID
 , 1 OULevel
 FROM dbo.V_DIM_01_OrganizationalUnits
 WHERE OU_ParentGUID = ''
 UNION ALL
 SELECT
  c.OU_GUID
 , c.OU_Name
 , c.OU_ParentGUID
 , p.OULevel + 1
 FROM dbo.V_DIM_01_OrganizationalUnits c
 JOIN cte p ON p.OU_GUID = c.OU_ParentGUID
)
SELECT * FROM cte

The structure could be

- "OU" => "US" => "User"
- "OU" => "MX" => "User"

- "OU" => "DE" => "User"
- "OU" => "PL" => "User"

Now I have an additional column "region" which I added via the view V_DIM_01_OrganizationalUnits.


SELECT [OU_GUID]
      ,[OU_ParentGUID]
      ,[OU_Path]
      ,[OU_Name]
      ,CASE 
      WHEN upper([OU_Name]) IN ('CH','CZ','DE','ES','FR','GB','IT','PL','RS','RU','SE','TR') THEN 'EMEA'
      WHEN upper([OU_Name]) IN ('BR','MX','US') THEN 'AMERICAS'
      WHEN upper([OU_Name]) IN ('AU','CN','IN','JP','MY','SG','TH') THEN 'APAC'
      END Region
  FROM [T_DIM_OrganizationalUnits]

The future structure should be

- "OU" => "Americas" => "US" => "User"
- "OU" => "Americas" => "MX" => "User"

- "OU" => "EMEA" => "DE" => "User"
- "OU" => "EMEA" => "PL" => "User"

How can I include the "region" into my statement as it is actually not a member of the real hierarchy.

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-27 : 14:10:33

if you've already in the view just include it in CTE definition


WITH cte AS (
 SELECT
  OU_GUID
 , OU_Name
 ,Region
 , OU_ParentGUID
 , 1 OULevel
 FROM dbo.V_DIM_01_OrganizationalUnits
 WHERE OU_ParentGUID = ''
 UNION ALL
 SELECT
  c.OU_GUID
 , c.OU_Name
 ,c.Region
 , c.OU_ParentGUID
 , p.OULevel + 1
 FROM dbo.V_DIM_01_OrganizationalUnits c
 JOIN cte p ON p.OU_GUID = c.OU_ParentGUID
)
SELECT * FROM cte

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources