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

Critical Update

When working with PostgreSQL, there is a known issue regarding aliases in inline SQL statements and datasets. Aliases written in any mixed or uppercase format (e.g., CamelCase, lowerCamelCase) are returned by the PostgreSQL driver as lowercase field names. Using these aliases in code (like DataRow["ColName"]) or as a Binding Path will lead to runtime errors or binding failures. To resolve this issue, consider the following change:

Fixing lowerCamelCase in In-Line SQL

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

Lateral Join

Scalar Subqueries

(not recommended) Subselect SQL

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