1. Standard example of CTE block:
WITH _data AS(
SELECT 1 AS val
)
SELECT val FROM _data;
-- return: 1
2. Example using mapping:
WITH YearCodes (year_code, year) AS (
VALUES
( 'Y', 2000 ),
( '1', 2001 ),
( '2', 2002 )
)
SELECT * FROM YearCodes
If you want to make your SQL readable and solve many performance questions first of all create CTE blocks. This simple and understandable solution solve for you many problems. But for better performance I am recommending to analyze your SQL scripts every time.
Example for PostgreSQL.
1. Standard example of CTE block:
WITH _data AS(
SELECT 1 AS val
)
SELECT val FROM _data;
-- return: 1
2. Example using mapping:
WITH YearCodes (year_code, year) AS (
VALUES
( 'Y', 2000 ),
( '1', 2001 ),
( '2', 2002 )
)
SELECT * FROM YearCodes