Sql-server – Hierarchy of Employee “names” with CTE: Cannot create a correct version

ctesql serversql-server-2012

I am trying to create the correct structure of my sql (CTE) query, but I can not. This is a version I want to have correct version .

And this is my current version: what i have now . As you can see there are duplicates and i have no idea how to get rid of them. Futhermore the structure is wrong. I already tried to look for solutions like here but was unable to come up with solution.
CTE query doesn't print the right structure?

My Employee table looks like this http://sqlfiddle.com/#!6/428d2/2

Here is my SQL query I use to create the correct name column: http://sqlfiddle.com/#!6/bf4c1/4

I use MsSQL 2012, so feel free to use newest fuctions etc. Any help appreciated.

Best Answer

If you specify the root node in the CTE, it works ok.

WITH Empl_Tab( Id ,
                 ParentId ,
                 LEVEL,
             [Order]
) AS ( SELECT Employee.[EMPl Id] ,  Employee.[reports to the Boss] ,
                0 AS LEVEL ,
              CONVERT([varchar](MAX), Employee.[EMPl Id]) AS [Order]
           FROM Employee
where [reports to the Boss] = 1
        UNION ALL

        SELECT Employee.[EMPl Id] ,
                Employee.[reports to the Boss] ,
                Empl_Tab.LEVEL+1 AS LEVEL ,
             Empl_Tab.[Order] + CONVERT([varchar](30), Employee.[EMPl Id]) AS [Order]
           FROM
                Employee INNER JOIN Empl_Tab
                ON Empl_Tab.Id = Employee.[reports to the Boss] 
     )

SELECT REPLICATE( '.' ,Empl_Tab.Id*1 )+Employee.Name AS Name
  FROM
       Employee INNER JOIN Empl_Tab
       ON Empl_Tab.Id = Employee.[EMPl Id]
  ORDER BY Empl_Tab.[Order]

SQL Fiddle here.

Related Solutions

Sql-server – How to avoid duplicating information in a series of nested SELECT statements

;WITH x AS 
(
  SELECT g.guestid, e.[year], e.run, c = COUNT(*)
  FROM dbo.guest AS g
  INNER JOIN dbo.[order] AS o
  ON g.guestid = o.guestid
  INNER JOIN dbo.eventseat AS es
  ON o.orderid = es.orderid
  INNER JOIN dbo.event2 AS e
  ON e.eventid = es.eventid
  WHERE e.incometype = 'T'
  AND e.eventtype in ('SS', 'BMF')
  AND es.status in ('2','4')
  AND es.price NOT IN ( '0.00', '5.00')
  AND e.[year] = '2010'
  AND e.run IN ('SS-DANCE', 'SS-FILM', 'SS-OPERA')
  GROUP BY g.guestid, e.[Year], e.run
),
y AS 
(
  SELECT * FROM x PIVOT (MAX(c) FOR run IN 
  ([SS-DANCE],[SS-FILM],[SS-OPERA])) AS z
)
SELECT y.guestid, y.[year], g.[first], g.[last], 
  [SS-DANCE] = COALESCE(y.[SS-DANCE], 0), 
  [SS-FILM]  = COALESCE(y.[SS-FILM], 0), 
  [SS-OPERA] = COALESCE(y.[SS-OPERA], 0)
FROM y 
INNER JOIN dbo.guest AS g
ON y.guestid = g.guestid;

I'm not sure if my where clause is going to work for you as written. This currently assumes you only care about these three types (dance, film and opera) and you only care about the year 2010. If you need to cross years you can remove that WHERE clause. If you only care about dance in 2010 and film in any year you'll need to write a more complex set of where clauses.

Some comments:

try to avoid reserved words like order, year, first and last. These only complicate matters because they need to be enclosed in square brackets (which are preferable over "double quotes" mostly for readability and unambiguity).
always use dbo. (or whatever schema prefix is appropriate).
as I suggested above, stick to explicit INNER JOIN syntax. FROM x, y, z is for the birds.

Oracle View Ignoring External WHERE Clause

With a bit of trial and error, I was able to spot the culprit. Turns out, it has nothing to do with the view itself. Attempting to manually push the where clause directly into the query also gave the same bad results.

As it turns out, the problem was cause by the CONNECT BY clause in the inner queries. My suspicion (which may be completely wrong) is that it has to do with the fact that Oracle 10g cannot handle recursive CTE's. If anyone has any more information about why this might have happened, I am all ears.

I was able to work around the issue by moving the

SELECT level AS UNPIVOT_ROW FROM DUAL CONNECT BY level <= 3

subquery to its own CTE block and selecting from that as normal. Below I have included the (condensed) working version of the above broken query.

CREATE OR REPLACE VIEW FSA.FSA_V_DB_TOTALS_2
(...)
AS
WITH
--Queries to generate intermediate result sets

--'NUMBERS' CTE block to work around Oracle 10g limitation wherein WHERE
--  clause is ignored if CONNECY BY is directly written into inner queries.
--  NOTE: the size limitation (currently 10) only needs to be larger than
--        or equal to the largest value needed.
NUMBERS AS (
SELECT level AS UNPIVOT_ROW
  FROM DUAL CONNECT BY level <=10
),

--Distribution TRA
DIST_TRA AS (
SELECT ...
  FROM (
       SELECT ...
         FROM FSA.FSA001_DISTRIBUT T1
              INNER JOIN
              FSA.FSA002_DIST_TRA T2 ON T1.SERIAL_NO = T2.SERIAL_NO
        GROUP BY GROUPING SETS (...)
        ),(
        SELECT UNPIVOT_ROW FROM NUMBERS WHERE UNPIVOT_ROW <= 3      -- <==  THIS
        )
),

--Distribution Fran
DIST_FRAN AS (
SELECT ...
  FROM (
       SELECT ...
         FROM FSA.FSA001_DISTRIBUT T1
              INNER JOIN
              FSA.FSA003_DIST_FRAN T3 ON T1.SERIAL_NO = T3.SERIAL_NO
        GROUP BY GROUPING SETS (...)
        ),(
        SELECT UNPIVOT_ROW FROM NUMBERS WHERE UNPIVOT_ROW <= 2      -- <==  THIS AS WELL
        )
)

--Queries to generate final result set based on CTE intermediate queries
--Subtotals / System totals for Dist-TRA, Dist-Fran, Trans-TRA, Trans-Fran
SELECT ...
  FROM (SELECT * FROM DIST_TRA
        UNION ALL
        SELECT * FROM DIST_FRAN)

Best Answer

Related Solutions

Sql-server – How to avoid duplicating information in a series of nested SELECT statements

Oracle View Ignoring External WHERE Clause

Related Question