Oracle View Ignoring External WHERE Clause

cteoracleoracle-10gview

Good day,

This one has me stumped. I have a rather nasty developer query that I would like to store in a non-materialized Oracle view. The text for the view itself is a bit long to list here, but it compiles just fine and generates the correct result set. The view query itself uses a CTE as well as two manual unpivots (the UNPIVOT operator is not available in Oracle 10) and quite a few UNIONS. The idea is to use each of the CTE 'intermediate' queries repeatedly to extract various aggregations that would otherwise not be reasonably possible in a single query (poor DB design, unfortunately out of my control). There are many aggregations required, and this is the best solution I could come up with in terms of speed, maintainability, and readability / self-documenting code.

Now, this view works perfectly when I do a simple

SELECT *
  FROM myView;

However, whenever I try to filter the results, the WHERE clause appears to be ignored. Something along the lines of

SELECT *
  FROM myView
 WHERE DATA_TYPE = 3;  --  <== There is no DATA_TYPE = 3 in the result set

Still returns all rows, while it should return nothing. Other predicates involving any of the other columns also appear to be ignored. Any idea what could possibly be causing this? The SELECT above executes just fine, and no errors are given.

For reference, the DBMS is Oracle 10g.

Thank you in advance for any assistance.

EDIT:

A trimmed down version of the view query that still demonstrates the same behavior is as follows (there are a lot more queries on both the inner and the outer portion of the CTE, I only included two inner and one outer for compactness):

CREATE OR REPLACE VIEW FSA.FSA_V_DB_TOTALS
(
    DATA_CATEGORY, DATA_TYPE, HUMAN_STRING, ELEMENT_NAME, ELEMENT_VAL,
    CLASS_CD, REGION, CNTY, DIST, TRA, FRAN, PROP
)
AS
WITH
--Queries to generate intermediate result sets
--Distribution TRA
DIST_TRA AS (
SELECT 'DISTRIBUTION - TRA' AS DATA_CATEGORY,
       1 AS DATA_TYPE,
       CLASS_CD AS CLASS_CD,
       NULL AS REGION,
       CNTY_CD AS CNTY,
       NULL AS DIST,
       TRA_CD AS TRA,
       NULL AS FRAN,
       NULL AS PROP,
       TEXT AS HUMAN_STRING,
       DECODE(UNPIVOT_ROW, 1, 'CIRCUIT MILEAGE',
                           2, 'WIRE MILEAGE',
                           3, 'DUCT MILEAGE',
                           'N/A') AS ELEMENT_NAME,
       DECODE(UNPIVOT_ROW, 1, CIRCT_FT,
                           2, WIRE_FT,
                           3, DUCT_FT,
                           'N/A') AS ELEMENT_VAL
  FROM (
       SELECT T1.CLASS_CD AS CLASS_CD,
              T1.CNTY_CD AS CNTY_CD,
              T2.TRA_CD AS TRA_CD,
              DECODE(COALESCE(T2.TRA_CD,
                              T1.CNTY_CD,
                              T1.CLASS_CD), NULL, 'DISTRIBUTION TRA TOTAL',
                                            T2.TRA_CD, 'TRA ' || T2.TRA_CD || ' TOTAL',
                                            T1.CNTY_CD, 'COUNTY ' || T1.CNTY_CD || ' TOTAL',
                                            T1.CLASS_CD, 'CLASS ' || T1.CLASS_CD || ' TOTAL') AS TEXT,
              SUM(T2.CIRCT_FT_QTY) AS CIRCT_FT,
              SUM(T2.WIRE_FT_QTY) AS WIRE_FT,
              SUM(T2.DUCT_FT_QTY) AS DUCT_FT
         FROM FSA.FSA001_DISTRIBUT T1
              INNER JOIN
              FSA.FSA002_DIST_TRA T2 ON T1.SERIAL_NO = T2.SERIAL_NO
        GROUP BY GROUPING SETS ((T1.CLASS_CD, T1.CNTY_CD, T2.TRA_CD),
                                (T1.CLASS_CD, T1.CNTY_CD),
                                (T1.CLASS_CD),
                                ())
        ),(
        SELECT level AS UNPIVOT_ROW FROM DUAL CONNECT BY level <= 3
        )
),

--Distribution Fran
DIST_FRAN AS (
SELECT 'DISTRIBUTION - FRAN' AS DATA_CATEGORY,
       2 AS DATA_TYPE,
       CLASS_CD AS CLASS_CD,
       NULL AS REGION,
       CNTY_CD AS CNTY,
       DIST_CD AS DIST,
       NULL AS TRA,
       FRAN_CD AS FRAN,
       PROP_CD AS PROP,
       TEXT AS HUMAN_STRING,
       DECODE(UNPIVOT_ROW, 1, 'POLE MILEAGE',
                           2, 'ST LIGHT MILEAGE',
                           'N/A') AS ELEMENT_NAME,
       DECODE(UNPIVOT_ROW, 1, POLE_FT,
                           2, SL_FT,
                           'N/A') AS ELEMENT_VAL
  FROM (
       SELECT T1.CLASS_CD AS CLASS_CD,
              T1.CNTY_CD AS CNTY_CD,
              T1.DIST_CD AS DIST_CD,
              T3.FRNCHSE_CD AS FRAN_CD,
              T3.PROPRTY_CD AS PROP_CD,
              DECODE(COALESCE(T3.PROPRTY_CD,
                              T3.FRNCHSE_CD,
                              T1.DIST_CD,
                              T1.CNTY_CD,
                              T1.CLASS_CD), NULL, 'DISTRIBUTION FRAN TOTAL',
                                            T3.PROPRTY_CD, 'PROPERTY ' || T3.PROPRTY_CD     || ' TOTAL',
                                            T3.FRNCHSE_CD, 'FRAN ' || T3.FRNCHSE_CD || ' TOTAL',
                                            T1.DIST_CD, 'DISTRICT ' || T1.DIST_CD || ' TOTAL',
                                            T1.CNTY_CD, 'COUNTY ' || T1.CNTY_CD || ' TOTAL',
                                            T1.CLASS_CD, 'CLASS ' || T1.CLASS_CD || ' TOTAL') AS TEXT,
              SUM(T3.POLE_FT_QTY) AS POLE_FT,
              SUM(T3.ST_LIGHT_FT_QTY) AS SL_FT
         FROM FSA.FSA001_DISTRIBUT T1
              INNER JOIN
              FSA.FSA003_DIST_FRAN T3 ON T1.SERIAL_NO = T3.SERIAL_NO
        GROUP BY GROUPING SETS ((T1.CLASS_CD, T1.CNTY_CD, T1.DIST_CD, T3.FRNCHSE_CD,     T3.PROPRTY_CD),
                                (T1.CLASS_CD, T1.CNTY_CD, T1.DIST_CD, T3.FRNCHSE_CD),
                                (T1.CLASS_CD, T1.CNTY_CD, T1.DIST_CD),
                                (T1.CLASS_CD, T1.CNTY_CD),
                                (T1.CLASS_CD),
                                ())
        ),(
        SELECT level AS UNPIVOT_ROW FROM DUAL CONNECT BY level <= 2
        )
)

--Queries to generate final result set based on CTE intermediate queries
--Subtotals / System totals for Dist-TRA, Dist-Fran, Trans-TRA, Trans-Fran
SELECT DATA_CATEGORY, DATA_TYPE, HUMAN_STRING, ELEMENT_NAME, ELEMENT_VAL,
       CLASS_CD, REGION, CNTY, DIST, TRA, FRAN, PROP
  FROM (SELECT * FROM DIST_TRA
        UNION ALL
        SELECT * FROM DIST_FRAN)

 ORDER BY DATA_CATEGORY, CLASS_CD, REGION, CNTY, DIST, NVL2(TRA, TRA, FRAN), ELEMENT_NAME, HUMAN_STRING
/

Edit 2:

The complete explain plan for selection from the view with a predicate. Note that the DBMS does not show any predicates on the top level SELECT.

SQL> SELECT * FROM FSA.FSA_V_DB_TOTALS WHERE DATA_TYPE = 3;

Execution Plan
----------------------------------------------------------
Plan hash value: 3417880006

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name             | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                  | 88095 |  6194K|       |  5232   (3)| 00:01:03 |
|   1 |  VIEW                              | FSA_V_DB_TOTALS  | 88095 |  6194K|       |  5232   (3)| 00:01:03 |
|   2 |   SORT ORDER BY                    |                  | 88095 |  6194K|    17M|  5232   (3)| 00:01:03 |
|   3 |    VIEW                            |                  | 88095 |  6194K|       |  3721   (4)| 00:00:45 |
|   4 |     UNION-ALL                      |                  |       |       |       |            |          |
|   5 |      MERGE JOIN CARTESIAN          |                  |  1995 |   150K|       |  1492   (6)| 00:00:18 |
|   6 |       VIEW                         |                  |     1 |    13 |       |     2   (0)| 00:00:01 |
|*  7 |        CONNECT BY WITHOUT FILTERING|                  |       |       |       |            |          |
|   8 |         FAST DUAL                  |                  |     1 |       |       |     2   (0)| 00:00:01 |
|   9 |       BUFFER SORT                  |                  |  1995 |   124K|       |  1492   (6)| 00:00:18 |
|  10 |        VIEW                        |                  |  1995 |   124K|       |  1490   (6)| 00:00:18 |
|  11 |         SORT GROUP BY ROLLUP       |                  |  1995 | 59850 |       |  1490   (6)| 00:00:18 |
|* 12 |          HASH JOIN                 |                  |   408K|    11M|  3280K|  1447   (3)| 00:00:18 |
|  13 |           TABLE ACCESS FULL        | FSA001_DISTRIBUT |   152K|  1488K|       |   292   (2)| 00:00:04 |
|  14 |           TABLE ACCESS FULL        | FSA002_DIST_TRA  |   408K|  7980K|       |   362   (5)| 00:00:05 |
|  15 |      MERGE JOIN CARTESIAN          |                  | 86100 |  5969K|       |  2229   (3)| 00:00:27 |
|  16 |       VIEW                         |                  |     1 |    13 |       |     2   (0)| 00:00:01 |
|* 17 |        CONNECT BY WITHOUT FILTERING|                  |       |       |       |            |          |
|  18 |         FAST DUAL                  |                  |     1 |       |       |     2   (0)| 00:00:01 |
|  19 |       BUFFER SORT                  |                  | 86100 |  4876K|       |  2229   (3)| 00:00:27 |
|  20 |        VIEW                        |                  | 86100 |  4876K|       |  2227   (3)| 00:00:27 |
|  21 |         SORT GROUP BY ROLLUP       |                  | 86100 |  2438K|    14M|  2227   (3)| 00:00:27 |
|* 22 |          HASH JOIN                 |                  |   267K|  7582K|  3728K|  1006   (3)| 00:00:13 |
|  23 |           TABLE ACCESS FULL        | FSA001_DISTRIBUT |   152K|  1935K|       |   293   (3)| 00:00:04 |
|  24 |           TABLE ACCESS FULL        | FSA003_DIST_FRAN |   267K|  4183K|       |   166   (5)| 00:00:02 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   7 - filter(LEVEL<=3)
  12 - access("T2"."SERIAL_NO"="T1"."SERIAL_NO")
  17 - filter(LEVEL<=2)
  22 - access("T1"."SERIAL_NO"="T3"."SERIAL_NO")

SQL>

Best Answer

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)

Related Solutions

Sql-server – Window functions cause awful execution plan when called from a view with external parametrized ‘where’ clause

This appears to be a long standing issue that keeps resurfacing in one form or another and is still present in SQL Server 2012.

Some posts discussing it are

All current versions of SQL Server up to and including 2012 are not able to push the filter on a partitioning group past the sequence project for a parameterised predicate except if option(recompile) is used (if 2008+).

An alternative to the recompile hint would be to rewrite the query to use a parameterised inline TVF as suggested by @a1ex07)

Sql-server – Optimizing a CTE hierarchy

Edit: this is second attempt

Based on @Hannah Vernon's answer, here is a way to bypass the use of CTE inside an inline subquery, which is like self-joining the CTE and I presume is the reason for the poor efficiency. It uses analytic functions available only in the 2012 version of SQL-Server. Tested at SQL-Fiddle

This part can be skipped from reading, it's a copy-paste from Hannah's answer:

;With AccountHierarchy AS
(                                                                           
    SELECT
        Root.AcctId                                         AccountId
        , Root.Name                                         AccountName
        , Root.ParentId                                     ParentId
        , 1                                                 HierarchyLevel  
        , cast(Root.Acctid as varchar(4000))                IdHierarchyMatch        
        , cast(Root.Acctid as varchar(4000))                IdHierarchy
        , cast(replace(Root.Name,'.','') as varchar(4000))  NameHierarchy   
        , cast(Root.Acctid as varchar(4000))                HierarchySort
        , cast(Root.Name as varchar(4000))                  HierarchyLabel          ,
        Root.CustomerCount                                  CustomerCount   

    FROM 
        account Root

    WHERE
        Root.ParentID is null

    UNION ALL

    SELECT
        Recurse.Acctid                                      AccountId
        , Recurse.Name                                      AccountName
        , Recurse.ParentId                                  ParentId
        , Root.HierarchyLevel + 1                           HierarchyLevel
        , CAST(CAST(Root.IdHierarchyMatch as varchar(40)) + '.' 
            + cast(recurse.Acctid as varchar(40))   as varchar(4000))   IdHierarchyMatch
        , cast(cast(recurse.Acctid as varchar(40)) + '.' 
            + Root.IdHierarchy  as varchar(4000))           IdHierarchy
        , cast(replace(recurse.Name,'.','') + '.' 
            + Root.NameHierarchy as varchar(4000))          NameHierarchy
        , cast(Root.AccountName + '.' 
            + Recurse.Name as varchar(4000))                HierarchySort   
        , cast(space(root.HierarchyLevel * 4) 
            + Recurse.Name as varchar(4000))                HierarchyLabel
        , Recurse.CustomerCount                             CustomerCount
    FROM
        account Recurse INNER JOIN
        AccountHierarchy Root on Root.AccountId = Recurse.ParentId
)

Here we order the rows of the CTE using the IdHierarchyMatch and we calculate row numbers and a running total (from the next row up to the end.)

, cte1 AS 
(
SELECT
    h.AccountId
    , h.AccountName
    , h.ParentId
    , h.HierarchyLevel
    , h.IdHierarchy
    , h.NameHierarchy
    , h.HierarchyLabel
    , parsename(h.IdHierarchy,1) Acct1Id
    , parsename(h.NameHierarchy,1) Acct1Name
    , parsename(h.IdHierarchy,2) Acct2Id
    , parsename(h.NameHierarchy,2) Acct2Name
    , parsename(h.IdHierarchy,3) Acct3Id
    , parsename(h.NameHierarchy,3) Acct3Name
    , parsename(h.IdHierarchy,4) Acct4Id
    , parsename(h.NameHierarchy,4) Acct4Name
    , h.CustomerCount
    , h.HierarchySort
    , h.IdHierarchyMatch
        , Rn = ROW_NUMBER() OVER 
                  (ORDER BY h.IdHierarchyMatch)
        , RunningCustomerCount = COALESCE(
            SUM(h.CustomerCount)
            OVER
              (ORDER BY h.IdHierarchyMatch
               ROWS BETWEEN 1 FOLLOWING
                        AND UNBOUNDED FOLLOWING)
          , 0) 
FROM
    AccountHierarchy AS h  
)

Then we have one more intermediate CTE where we use the previous running totals and row numbers - basically to find where the end points for the branches of the tree structure:

, cte2 AS
(
SELECT
    cte1.*
    , rn3  = LAST_VALUE(Rn) OVER 
               (PARTITION BY Acct1Id, Acct2Id, Acct3Id 
                ORDER BY Acct4Id
                ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)       
    , rn2  = LAST_VALUE(Rn) OVER 
               (PARTITION BY Acct1Id, Acct2Id 
                ORDER BY Acct3Id, Acct4Id
                ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 
    , rn1  = LAST_VALUE(Rn) OVER 
               (PARTITION BY Acct1Id 
                ORDER BY Acct2Id, Acct3Id, Acct4Id
                ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 
    , rcc3 = LAST_VALUE(RunningCustomerCount) OVER 
               (PARTITION BY Acct1Id, Acct2Id, Acct3Id 
                ORDER BY Acct4Id
                ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)       
    , rcc2 = LAST_VALUE(RunningCustomerCount) OVER 
               (PARTITION BY Acct1Id, Acct2Id 
                ORDER BY Acct3Id, Acct4Id
                ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 
    , rcc1 = LAST_VALUE(RunningCustomerCount) OVER 
               (PARTITION BY Acct1Id 
                ORDER BY Acct2Id, Acct3Id, Acct4Id
                ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 
FROM
    cte1 
)

and finally we build the last part:

SELECT
    hier.AccountId
    , hier.AccountName
    ---                        -- columns skipped 
    , hier.CustomerCount

    , TotalCustomerCount = hier.CustomerCount
        + hier.RunningCustomerCount 
        - ca.LastRunningCustomerCount

    , hier.HierarchySort
    , hier.IdHierarchyMatch
FROM
    cte2 hier
  OUTER APPLY
    ( SELECT  LastRunningCustomerCount, Rn
      FROM
      ( SELECT LastRunningCustomerCount
              = RunningCustomerCount, Rn
        FROM (SELECT NULL a) x  WHERE 4 <= HierarchyLevel 
      UNION ALL
        SELECT rcc3, Rn3
        FROM (SELECT NULL a) x  WHERE 3 <= HierarchyLevel 
      UNION ALL
        SELECT rcc2, Rn2 
        FROM (SELECT NULL a) x  WHERE 2 <= HierarchyLevel 
      UNION ALL
        SELECT rcc1, Rn1
        FROM (SELECT NULL a) x  WHERE 1 <= HierarchyLevel 
      ) x
      ORDER BY Rn 
      OFFSET 0 ROWS
      FETCH NEXT 1 ROWS ONLY
      ) ca
ORDER BY
    hier.HierarchySort ;

And a simplification, using the same cte1 as the code above. Test at SQL-Fiddle-2. Please note that both solutions work under the assumption that you have a maximum of four levels in your tree:

SELECT
    hier.AccountId
    ---                      -- skipping rows
    , hier.CustomerCount

    , TotalCustomerCount = CustomerCount
        + RunningCustomerCount 
        - CASE HierarchyLevel
            WHEN 4 THEN RunningCustomerCount
            WHEN 3 THEN LAST_VALUE(RunningCustomerCount) OVER 
                   (PARTITION BY Acct1Id, Acct2Id, Acct3Id 
                    ORDER BY Acct4Id
                    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)       
            WHEN 2 THEN LAST_VALUE(RunningCustomerCount) OVER 
                   (PARTITION BY Acct1Id, Acct2Id 
                    ORDER BY Acct3Id, Acct4Id
                    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 
            WHEN 1 THEN LAST_VALUE(RunningCustomerCount) OVER 
                   (PARTITION BY Acct1Id 
                    ORDER BY Acct2Id, Acct3Id, Acct4Id
                    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 
          END

    , hier.HierarchySort
    , hier.IdHierarchyMatch
FROM cte1 AS hier
ORDER BY
    hier.HierarchySort ;

A third approach, with only one CTE, for the recursive part and then only window aggregate functions (SUM() OVER (...)), so it should work in any version from 2005 upwards. Test at SQL-Fiddle-3 This solution assumes, like the previous ones, that there are 4 levels maximum in the hierarchy tree:

;WITH AccountHierarchy AS
(                                                                           
    SELECT
          AccountId      = Root.AcctId                                         
        , AccountName    = Root.Name                                         
        , ParentId       = Root.ParentId                                     
        , HierarchyLevel = 1                                                   
        , HierarchySort  = CAST(Root.Acctid AS VARCHAR(4000))                
        , HierarchyLabel = CAST(Root.Name AS VARCHAR(4000))                   
        , Acct1Id        = CAST(Root.Acctid AS VARCHAR(4000))                
        , Acct2Id        = CAST(NULL AS VARCHAR(4000))                       
        , Acct3Id        = CAST(NULL AS VARCHAR(4000))                       
        , Acct4Id        = CAST(NULL AS VARCHAR(4000))                       
        , Acct1Name      = CAST(Root.Name AS VARCHAR(4000))                  
        , Acct2Name      = CAST(NULL AS VARCHAR(4000))                       
        , Acct3Name      = CAST(NULL AS VARCHAR(4000))                       
        , Acct4Name      = CAST(NULL AS VARCHAR(4000))                       
        , CustomerCount  = Root.CustomerCount                                   

    FROM 
        account AS Root

    WHERE
        Root.ParentID IS NULL

    UNION ALL

    SELECT
          Recurse.Acctid 
        , Recurse.Name 
        , Recurse.ParentId 
        , Root.HierarchyLevel + 1 
        , CAST(Root.AccountName + '.' 
            + Recurse.Name AS VARCHAR(4000)) 
        , CAST(SPACE(Root.HierarchyLevel * 4) 
            + Recurse.Name AS VARCHAR(4000)) 
        , Root.Acct1Id 
        , CASE WHEN Root.HierarchyLevel = 1 
              THEN cast(Recurse.Acctid AS VARCHAR(4000)) 
              ELSE Root.Acct2Id 
          END 
        , CASE WHEN Root.HierarchyLevel = 2 
              THEN CAST(Recurse.Acctid AS VARCHAR(4000)) 
              ELSE Root.Acct3Id 
          END 
        , CASE WHEN Root.HierarchyLevel = 3 
              THEN CAST(Recurse.Acctid AS VARCHAR(4000)) 
              ELSE Root.Acct4Id 
          END 
 
        , cast(Root.AccountName as varchar(4000))          
        , CASE WHEN Root.HierarchyLevel = 1 
              THEN CAST(Recurse.Name AS VARCHAR(4000)) 
              ELSE Root.Acct2Name 
          END 
        , CASE WHEN Root.HierarchyLevel = 2 
              THEN CAST(Recurse.Name AS VARCHAR(4000)) 
              ELSE Root.Acct3Name 
          END 
        , CASE WHEN Root.HierarchyLevel = 3 
              THEN CAST(Recurse.Name AS VARCHAR(4000)) 
              ELSE Root.Acct4Name 
          END 
        , Recurse.CustomerCount 
    FROM 
        account AS Recurse INNER JOIN 
        AccountHierarchy AS Root ON Root.AccountId = Recurse.ParentId
)

SELECT
      h.AccountId
    , h.AccountName
    , h.ParentId
    , h.HierarchyLevel
    , IdHierarchy = 
          CAST(COALESCE(h.Acct4Id+'.','') 
               + COALESCE(h.Acct3Id+'.','') 
               + COALESCE(h.Acct2Id+'.','') 
               + h.Acct1Id AS VARCHAR(4000))
    , NameHierarchy = 
          CAST(COALESCE(h.Acct4Name+'.','') 
               + COALESCE(h.Acct3Name+'.','') 
               + COALESCE(h.Acct2Name+'.','') 
               + h.Acct1Name AS VARCHAR(4000))   
    , h.HierarchyLabel
    , h.Acct1Id
    , h.Acct1Name
    , h.Acct2Id
    , h.Acct2Name
    , h.Acct3Id
    , h.Acct3Name
    , h.Acct4Id
    , h.Acct4Name
    , h.CustomerCount
    , TotalCustomerCount =  
          CASE h.HierarchyLevel
            WHEN 4 THEN h.CustomerCount
            WHEN 3 THEN SUM(h.CustomerCount) OVER 
                   (PARTITION BY h.Acct1Id, h.Acct2Id, h.Acct3Id)       
            WHEN 2 THEN SUM(h.CustomerCount) OVER 
                   (PARTITION BY Acct1Id, h.Acct2Id) 
            WHEN 1 THEN SUM(h.CustomerCount) OVER 
                   (PARTITION BY h.Acct1Id) 
          END
    , h.HierarchySort
    , IdHierarchyMatch = 
          CAST(h.Acct1Id 
               + COALESCE('.'+h.Acct2Id,'') 
               + COALESCE('.'+h.Acct3Id,'') 
               + COALESCE('.'+h.Acct4Id,'') AS VARCHAR(4000))   
FROM
    AccountHierarchy AS h  
ORDER BY
    h.HierarchySort ;

A 4th approach, that calculates as an intermediate CTE, the closure table of the hierarchy. Test at SQL-Fiddle-4. The benefit is that for the sums calculations, there is no resctriction on the number of levels.

;WITH AccountHierarchy AS
( 
    -- skipping several line, identical to the 3rd approach above
)

, ClosureTable AS
( 
    SELECT
          AccountId      = Root.AcctId  
        , AncestorId     = Root.AcctId  
        , CustomerCount  = Root.CustomerCount 
    FROM 
        account AS Root

    UNION ALL

    SELECT
          Recurse.Acctid 
        , Root.AncestorId 
        , Recurse.CustomerCount
    FROM 
        account AS Recurse INNER JOIN 
        ClosureTable AS Root ON Root.AccountId = Recurse.ParentId
)

, ClosureGroup AS
(                                                                           
    SELECT
          AccountId           = AncestorId  
        , TotalCustomerCount  = SUM(CustomerCount)                             
    FROM 
        ClosureTable AS a
    GROUP BY
        AncestorId
)

SELECT
      h.AccountId
    , h.AccountName
    , h.ParentId
    , h.HierarchyLevel 
    , h.HierarchyLabel
    , h.CustomerCount
    , cg.TotalCustomerCount 

    , h.HierarchySort
FROM
    AccountHierarchy AS h  
  JOIN
    ClosureGroup AS cg
      ON cg.AccountId = h.AccountId
ORDER BY
    h.HierarchySort ;

Best Answer

Related Solutions

Sql-server – Window functions cause awful execution plan when called from a view with external parametrized ‘where’ clause

Sql-server – Optimizing a CTE hierarchy

Related Question