Skip to Content

CTE block will make your code clear and readable

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


3. Full real examples create a reports:

WITH yearCTE(yearDual) AS (SELECT :taxYear FROM dual)

, yearsMapCTE(year) AS (
-- by business case previous 5 years required (from -1 to -5 from current),
-- this CTE just have it and used in other CTE as logical block for representation
SELECT yearDual - 5 FROM yearCTE
UNION ALL SELECT yearDual - 4 FROM yearCTE
UNION ALL SELECT yearDual - 3 FROM yearCTE
UNION ALL SELECT yearDual - 2 FROM yearCTE
UNION ALL SELECT yearDual - 1 FROM yearCTE)

, mainTmpCTE AS (
-- this CTE return all 5 years data for specific source, goal is to have whole those data as history to check with xxx data
-- Important note for this CTE block: return could have revisions, so we need to take only last revision (document) for each year. Logic done in mainCTE block
SELECT
return.LIABILITY_NUMBER AS liabilityNumber,
return.TAX_YEAR AS taxYear,
doc.DOCUMENT_NUMBER AS docNumber,
REGEXP_SUBSTR(doc.DOCUMENT_NUMBER, '[^-]+', 1, 1) AS docNumberPart1, -- need this to know if document (revision) is last
COALESCE(CAST(REGEXP_SUBSTR(doc.DOCUMENT_NUMBER, '[^-]+', 1, 2) as number), 0) AS docNumberPart2, -- need this to know if document (revision) is last
doc.FINALIZED AS docFinalized,
source.id AS sourceId,
COALESCE(adj.LOSS_CARRIED_FORWARD, 0) AS lossCarriedToNextYear
FROM ....)

, mainCTE AS (
-- block will return only last document (revised as approved or draft)
SELECT mainRanked.* FROM (
SELECT mainTmpCTE.*, rank() over (partition by mainTmpCTE.docNumberPart1 order by mainTmpCTE.docNumberPart2 desc) rnk FROM mainTmpCTE) mainRanked
WHERE mainRanked.rnk = 1)

...
...

, combineHistoryCTE AS (
SELECT * FROM (
SELECT
histTaxYear,
prevLossCarriedAmount,
ROW_NUMBER() OVER (PARTITION BY histTaxYear ORDER BY source) AS rn
FROM (
SELECT 1 AS source, histTaxYear, prevLossCarriedAmount FROM histLossCTE WHERE histLossCTE.rnk = 1
UNION ALL
SELECT 2 AS source, histTaxYear, prevLossCarriedAmount FROM prevLossCTE
)
) WHERE rn = 1)

SELECT
yearsMapCTE.year AS year,
COALESCE(combineHistoryCTE.prevLossCarriedAmount, 0.00) AS previousLossCarriedAmount
FROM yearsMapCTE
LEFT JOIN currentMainCTE ON yearsMapCTE.year = currentMainCTE.taxYear - 1
LEFT JOIN combineHistoryCTE ON yearsMapCTE.year = combineHistoryCTE.histTaxYear;


Data modeling rules