Alias referencing

Important Notes

• Aliases defined in the SELECT clause cannot be referenced in the WHERE clause (this is due to SQL's logical processing order)

• Aliases can be referenced in GROUP BY, HAVING, and ORDER BY clauses

• For column aliases with spaces or special characters, use double quotes

For cases where your V16 Statement references aliases in WHERE clause, below are 3 possible options of re-writing the SQL:

V16 SQL

SELECT
"art"."artnr",
"art"."bez1",
"art"."bez2",
"art"."suchwort",
"art"."artgrp",
"art"."zeich_nr",
"art"."me_nr",
"art"."werkstoff",
"mm_grid"."masch_id" AS mm_grid_masch_id,
"mm_grid"."wz_id" AS mm_grid_wz_id,
"mm_grid"."oberflaeche" AS mm_grid_oberflaeche,
(SELECT me.bez FROM me WHERE me.nr = "art"."me_nr") AS cf_me_bez
FROM
"art"
LEFT OUTER JOIN "mm_grid" ON ("art"."mm_grid_nr" = "mm_grid"."nr")
where  ( cf_me_bez LIKE '%buc%' )  
ORDER BY artnr

---

V17 possible conversions

Common Table Expression (CTE)

The fix includes creating a Common Table Expression (CTE) named dt with all selected columns.

The query will:

• Filter that CTE for records where cf_me_bez contains 'buc'

• Order the results by artnr from the CTE

WITH dt AS (
    

SELECT
    "art"."artnr",
    "art"."bez1",
    "art"."bez2",
    "art"."suchwort",
    "art"."artgrp",
    "art"."zeich_nr",
    "art"."me_nr",
    "art"."werkstoff",
    "mm_grid"."masch_id" AS mm_grid_masch_id,
    "mm_grid"."wz_id" AS mm_grid_wz_id,
    "mm_grid"."oberflaeche" AS mm_grid_oberflaeche,
    (SELECT "me"."bez" FROM "me" WHERE "me"."nr" = "art"."me_nr") AS cf_me_bez
    FROM
    "art"
    LEFT OUTER JOIN "mm_grid" ON ("art"."mm_grid_nr" = "mm_grid"."nr")

)
SELECT * 
FROM dt
WHERE dt.cf_me_bez LIKE '%buc%'
ORDER BY artnr

Nested CTE

The nested CTE approach can improve readability and might offer better performance in some database systems by breaking down the query into logical components.

This query will:

• Create a first CTE (art_with_mm_grid) that handles the join between art and mm_grid

• Creates a second CTE (art_with_me_units) that adds the measurement unit description via a subquery

• The main query then filters and sorts the results

WITH art_with_mm_grid AS (
    SELECT
        "art"."artnr",
        "art"."bez1",
        "art"."bez2",
        "art"."suchwort",
        "art"."artgrp",
        "art"."zeich_nr",
        "art"."me_nr",
        "art"."werkstoff",
        "mm_grid"."masch_id" AS mm_grid_masch_id,
        "mm_grid"."wz_id" AS mm_grid_wz_id,
        "mm_grid"."oberflaeche" AS mm_grid_oberflaeche
    FROM
        "art"
    LEFT OUTER JOIN "mm_grid" ON ("art"."mm_grid_nr" = "mm_grid"."nr")
),
art_with_me_units AS (
    SELECT
        a.*,
        (SELECT "me"."bez" FROM "me" WHERE "me"."nr" = a."me_nr") AS me_bez
    FROM
        art_with_mm_grid a
)
SELECT * 
FROM art_with_me_units
WHERE me_bez LIKE '%buc%'
ORDER BY artnr

Lateral Join

The fix includes converting the subquery in the SELECT clause to a LATERAL join. The LATERAL join allows us to reference columns from the left side ("art"."me_nr") in the right side subquery.

SELECT
    "art"."artnr",
    "art"."bez1",
    "art"."bez2",
    "art"."suchwort",
    "art"."artgrp",
    "art"."zeich_nr",
    "art"."me_nr",
    "art"."werkstoff",
    "mm_grid"."masch_id" AS mm_grid_masch_id,
    "mm_grid"."wz_id" AS mm_grid_wz_id,
    "mm_grid"."oberflaeche" AS mm_grid_oberflaeche,
    "m"."bez" AS cf_me_bez
FROM
    "art"
LEFT OUTER JOIN "mm_grid" ON ("art"."mm_grid_nr" = "mm_grid"."nr")
LEFT JOIN LATERAL (
    SELECT "me"."bez" 
    FROM "me" 
    WHERE "me"."nr" = "art"."me_nr"
) AS m ON true
WHERE "m"."bez" LIKE '%buc%'
ORDER BY "art"."artnr";

Scalar Subqueries

Fixed the WHERE clause to reference the actual subquery rather than the alias (PostgreSQL doesn't allow referencing column aliases in the WHERE clause)

SELECT
"art"."artnr",
"art"."bez1",
"art"."bez2",
"art"."suchwort",
"art"."artgrp",
"art"."zeich_nr",
"art"."me_nr",
"art"."werkstoff",
"mm_grid"."masch_id" AS mm_grid_masch_id,
"mm_grid"."wz_id" AS mm_grid_wz_id,
"mm_grid"."oberflaeche" AS mm_grid_oberflaeche,
(SELECT "me"."bez" FROM "me" WHERE "me"."nr" = "art"."me_nr") AS cf_me_bez
FROM
"art"
LEFT OUTER JOIN "mm_grid" ON ("art"."mm_grid_nr" = "mm_grid"."nr")
WHERE (SELECT "me"."bez" FROM "me" WHERE "me"."nr" = "art"."me_nr") LIKE '%buc%'
ORDER BY "art"."artnr"

(not recommended) Subselect SQL

Attention, we highly recommend not using this version.

The fix includes maintaining the structure by using a derived table (subquery) dt.

The query will correctly:

• Create a derived table with all your selected columns

• Filter that derived table for records where cf_me_bez contains 'buc'

• Order the results by artnr

SELECT * FROM (
    SELECT
    "art"."artnr",
    "art"."bez1",
    "art"."bez2",
    "art"."suchwort",
    "art"."artgrp",
    "art"."zeich_nr",
    "art"."me_nr",
    "art"."werkstoff",
    "mm_grid"."masch_id" AS mm_grid_masch_id,
    "mm_grid"."wz_id" AS mm_grid_wz_id,
    "mm_grid"."oberflaeche" AS mm_grid_oberflaeche,
    (SELECT "me"."bez" FROM "me" WHERE "me"."nr" = "art"."me_nr") AS cf_me_bez
    FROM
    "art"
    LEFT OUTER JOIN "mm_grid" ON ("art"."mm_grid_nr" = "mm_grid"."nr")
) AS dt
WHERE dt.cf_me_bez LIKE '%buc%'
ORDER BY artnr

Key Differences Summarized

Feature	Scalar Subquery	Common Table Expression (CTE)	LATERAL Join
Return Value	Single value (1 row, 1 column)	Result set (multiple rows/columns)	Result set (multiple rows/columns) for each outer row
Placement	SELECT list, WHERE, HAVING, expressions	WITH clause at query start	FROM clause (with JOIN LATERAL)
Correlation	Can be correlated or non-correlated	Cannot directly reference outer query columns	Explicitly correlated to preceding tables
Reusability	No direct reuse of the subquery itself	Yes, can be referenced multiple times	No direct reuse of the LATERAL subquery's definition (it's tied to the JOIN)
Primary Goal	Get a single value for an expression/comparison	Modularize complex queries, enable recursion	Row-dependent set retrieval ("Top-N per group")
Analogy	A function call returning a single value	A named view for a single query	A function call that generates a table for each row

When to Use Each

Feature	Best For	Performance Notes
CTE	Breaking down complex queries	Materialized separately
LATERAL Join	Row-wise joins with correlations	Optimized like a join
Scalar Subquery	Simple single-value lookups	Slow for large datasets