CTE – Common Table Expression in PL-SQL

CTE – Learn how to use a temporary query  using with Clause for referencing the select , insert, update, delete statements.

CTE

• CTE stands for Common Table Expression.
• It stores the temporary Named result set of a select query.
• This is derived from a simple query and defined with execution scope of a single  select, insert,update or delete statements.
• CTE clause can also be used in a CREATE VIEW statement as part of its defining select statement.
• A CTE  can references to itself and its called as Recursive CTE.

Syntax

With [CTE_NAME]
AS
(
--CTE QUERY DEFINITION
)

SELECT [COLUMN_LIST] FROM CTE_NAME

For base table data reference please click on the following URL STD

Query to find the Nth Highest Fee Payer

Usually we can write the query in the following with out CTE to find the Nth Highest FEE payer.

Query using Co-relate Sub query

SELECT SNAME,
  FEE
FROM STD S
WHERE 1=
  (SELECT COUNT(FEE) FROM STD S1 WHERE S1.FEE>=S.FEE
  );

Query using inline views with DENSE_RANK function

SELECT *
FROM
  ( SELECT SNAME,FEE,DENSE_RANK() OVER(ORDER BY FEE DESC) RNK FROM STD
  )
WHERE RNK=1;

Query using CTE  with ROW_NUMBER() function

--Named CTE with temporary result set
WITH NTH_HIGHEST AS
  ( SELECT SNAME,FEE, row_number() over ( order by FEE DESC) RNK FROM STD
  )

--select query using Named CTE
SELECT * FROM NTH_HIGHEST WHERE RNK=1;

Below is the output for all the above queries

Output

Parametrized CTE

Syntax

With [CTE_NAME](Parameter1,parameter2...)
AS
(
--CTE QUERY DEFINITION
  select column1,colum2... from TBL WHERE [CONDITION]
)

SELECT [COLUMN_LIST] FROM CTE_NAME

Query using  CTE  with parameter passing

WITH NTH_HIGHEST (STDNAME, STDFEE, LEVELOFRANK) AS
( SELECT SNAME,FEE, row_number() over ( order by FEE DESC) RNK FROM STD
)

If we observe the query, no of parameters in the with class of CTE is same as no of columns in the select query.

SELECT STDNAME, STDFEE, LEVELOFRANK FROM NTH_HIGHEST WHERE LEVELOFRANK=1;

This parameters now can behave as columns when using CTE with select query as shown above query.

Notes:

• when we define the CTE, we give a name to the result set and as well as to its columns.
• We have to use this CTE with next immediate staetment of select , insert, update or delete statement other wise it will result into exception stating that “table or view does not exist”