Common Table Expression in SQL Server

Common Table Expressions are introduced in SQL Server 2005. They are very useful when we are working with sub queries.

Sub queries are cumbersome to use when all the filtering and matching logic has to be implemented into the sub query expressions. Sub queries are not reusable. Data in sub query is available till it gives the result set to the user. Sub queries are useful when we have a single task to perform and need to use the query only once.

A work around to the limitation of sub query is to create and populate the sub query contents into temporary table. But, it needs permissions to create and use. We can use this temporary table as a physical table and we can apply join and filtering expressions on this. But, it is costly with respect to performance and it is overhead to physical disk reads.

CTE is best compared to both sub queries and temporary tables. It is a named object which can be reused and referenced like a physical table which gives flexibility to perform operations. It is a sub query that exists only in memory and does not require permissions to use and won’t perform unnecessary disk operations.  

Syntax for creating CTE:

WITH <CTE_Name> (<Col1>,<Col2>,…)

AS

( <Select Statement> )

 

CTE should be defined before it is used in the query. It should start with the key word WITH and the output column names list with in the parentheses and followed by SELECT statement.

Following statement selects all the male customers who are married. (I am taking AdventureWorksDW2012 for the demo purpose) -

WITH Customer_Details ([CustomerKey],[Customer_Full_Name])

AS

(select [CustomerKey],[FirstName]+' '+

      ISNULL([MiddleName],'') +' '+

      ISNULL([LastName],'') as [Customer_Full_Name]

         from [AdventureWorksDW2012].[dbo].[DimCustomer]

         where [Gender]='M' and [MaritalStatus]='M')

 

 

Above script alone will not run by itself. We have to use it in the subsequent query. If we try to execute the query, it throws invalid syntax error message. After defining the CTE, we can use it as a table. Columns that are used for joining, filtering and sorting outside the CTE, should be defined in the output column list with in parentheses.

Following Statement will give total quantities sold by county and promotion.

WITH Customer_Details ([CustomerKey],[Customer_Full_Name])

AS

(select [CustomerKey],[FirstName]+' '+

      ISNULL([MiddleName],'') +' '+

      ISNULL([LastName],'') as [Customer_Full_Name]

         from [AdventureWorksDW2012].[dbo].[DimCustomer]

         where [Gender]='M' and [MaritalStatus]='M')

 

  Select

  ST.[SalesTerritoryCountry] as Country,

  DP.[EnglishPromotionName] as Promotion_Name,

  sum(IST.[OrderQuantity]) as Sold_Quantity

  from  [AdventureWorksDW2012].[dbo].[DimSalesTerritory] as ST

  inner join [FactInternetSales] as IST

  on IST.[SalesTerritoryKey]=ST.[SalesTerritoryAlternateKey]

  inner join Customer_Details as cd

  on cd.[CustomerKey]=IST.[CustomerKey]

  inner join [AdventureWorksDW2012].[dbo].[DimPromotion] DP

  on DP.PromotionKey=IST.PromotionKey

  group by [SalesTerritoryCountry],DP.[EnglishPromotionName]

  order by 1

 

Output is –

Country	Promotion_Name	Sold_Quantity
Australia	No Discount	3591
Australia	Touring-3000 Promotion	1
Australia	Volume Discount 11 to 14	139
Canada	No Discount	2542
Canada	Touring-1000 Promotion	1
Canada	Touring-3000 Promotion	1
Canada	Volume Discount 11 to 14	92
France	No Discount	1308
France	Volume Discount 11 to 14	48
Germany	No Discount	1383
Germany	Touring-3000 Promotion	1
Germany	Volume Discount 11 to 14	46
United Kingdom	No Discount	1866
United Kingdom	Touring-1000 Promotion	1
United Kingdom	Volume Discount 11 to 14	75
United States	No Discount	6189
United States	Touring-1000 Promotion	4
United States	Touring-3000 Promotion	1

 

Let’s discuss how to use CTE as recursive in the next post.

Happy Learning.

 

 

Hierarchy Function in QlikView

There are many instances we see hierarchical data in tables with self-referential integrity constraint. If we want to see the data with levels, we have to issue complex SQL’s and call them from Qlikview script. This needs good knowledge in writing the SQL’s.   

Qlikview has a function called - Hierarchy, which is used to display the data in hierarchical manner. This is possible only when there is self-referential integrity data.  Following is the syntax of Hierarchy machine.

Hierarchy

 

The hierarchy prefix is used to transform a hierarchy table to a table that is useful in a Qlik Sense data model. It can be put in front of a LOAD or a SELECT statement and will use the result of the loading

statement as input for a table transformation.

 

Hierarchy  (NodeID,  ParentID,  NodeName,  [ParentName],  [PathSource], [PathName],  [PathDelimi         ter],  [Depth])(load_statement  | select_statement)

I have taken EMP and DEPT tables from Oracle’s demo database for explaining hierarchy function. Following screenshot shows the data in both the tables.  You can see there is an self-referential integrity constraint between EMPNO and MGR columns. MGR column has the EMPNO of  the respective manager. This column (MGR) is referring EMPNO column. EMPNO acts as Parent and MGR as Child.

Following are the steps involved in this.

1. Open and create the script in Qlikview. Create an OLEDB connection to connect to Oracle schema.

1. Import EMP table from Oracle Schema.

1. Call Hierarchy function and pass the parameters as follows.
    
   Hierarchy(EMPNO,MGR,ENAME,MGR_NAME,ENAME,HIERARCHICAL_PATH, '-->', 'Level')
   Arguments:
   1^st Argument : Node  à EMPNO
   2^nd Argument : Parent_Key à MGR
   3^rd Argument : Node_Name à ENAME
   4^th Argument : Parent_Name à MGR_NAME
   5^th Argument : Path_Source à ENAME
   6^th Argument : Path_Name à HIERARCHICAL_PATH
   7^th Argument : Path_Delimiter à  '-->'
   8^th Argument :  Depth à 'Level'
    

1. I have derived a column which calculates total salary.
   SAL+IF(IsNull(COMM),0,COMM) as Total_Sal

1. Import DEPT table from database just to get DNAME by joining them.

1. Following is a sample table box which shows the employee names in the order by levels. Qlikview appends number in sequence to the column we are defining. Following has the hierarchical path (with à as separator)  and also the level which we have derived through Hierarchy function.

 Following is the output if we want to see in the order by level.

Your comments and suggestions are highly appreciated.

Happy Learning.