Home > SQL Challenge > SQL – Common Table Expression (CTE)

SQL – Common Table Expression (CTE)

CTE exist in DB2 long before SQL Server 2005 introduced it. A CTE is a "temporary result set" that exists only within the scope of a single SQL statement. It allows access to functionality within that single SQL statement that was previously only available through use of functions, temp tables, cursors, and so on.

with myCTE as (
select col = ‘How are you’ )

select * from myCTE

Like a derived table, a CTE lasts only for the duration of a query but, in contrast to a derived table, a CTE can be referenced multiple times in the same query. So, we now we have a way of calculating percentages and performing arithmetic using aggregates without repeating queries or using a temp table.

CTE and recursion

More interesting, is the use of recursion with CTEs. The table defined in the CTE can be referenced in the CTE itself to give a recursive expression, using union all. Here is an example:

WITH QUEUE_HIERARCHY (QUEUE_ID, DEPT_ID, NM, QUEUE_DESC, PARENT_QUEUE_ID, WATERFALL_ORDER)
         AS   
         (    SELECT    QUEUE_ID
            ,        DEPT_ID
            ,        NM
            ,        QUEUE_DESC
            ,        PARENT_QUEUE_ID
            ,        WATERFALL_ORDER
            FROM    DB2SCHEMA.T_QUEUE_DEF 
            WHERE    PARENT_QUEUE_ID IN ( 90301982)
            UNION ALL
            SELECT    A.QUEUE_ID
            ,        A.DEPT_ID
            ,        A.NM
            ,        A.QUEUE_DESC
            ,        A.PARENT_QUEUE_ID
            ,        A.WATERFALL_ORDER
            FROM    DB2SCHEMA.T_QUEUE_DEF AS A, QUEUE_HIERARCHY AS B
            WHERE    A.PARENT_QUEUE_ID = B.QUEUE_ID
         )
         SELECT * FROM QUEUE_HIERARCHY ORDER BY PARENT_QUEUE_ID, NM

Categories: SQL Challenge
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: