Leveling Up with SQL: Advanced Techniques for Transforming Data into Insights

© The Author(s), under exclusive license to APress Media, LLC, part of Springer Nature 2023

M. SimonLeveling Up with SQLhttps://doi.org/10.1007/978-1-4842-9685-1_9

9. More on Common Table Expressions

Mark Simon¹  

(1)

Ivanhoe VIC, VIC, Australia

 

You have already made use of CTEs to prepare data for use in aggregates and other operations.

Here, we will take a further look at some of the more powerful features of CTEs.

CTEs As Variables

In Chapter 4, we tested some calculations with a test value:

WITH vars AS (

    SELECT ' abcdefghijklmnop ' AS string

    --  FROM dual   --  Oracle

)

SELECT

    string,

    --  sample string functions

FROM vars;

Later in this chapter, we’ll see a more sophisticated version of this technique when we look at table ...