3 ms·
MSSQL has the same (very frustrating) limitation. The obvious and ugly solution would be to wrap in a subquery (bonus: still std sql compliant, and performs wel
by infogulch 9y ago
MSSQL has the same (very frustrating) limitation. The obvious and ugly solution would be to wrap in a subquery (bonus: still std sql compliant, and performs well):
SELECT bigname
FROM (
SELECT UPPER(name) AS bigname
FROM people
) x
WHERE bigname = 'JOE'
Add columns as necessary, it should work fine.
But this method can make large queries with lots of aliasing very difficult to read and lead to deep nesting. There is an alternative in MSSQL and (via a quick search) pgsql as well to keep the crazy nesting to a minimum:
-- MSSQL
SELECT bigname
FROM people
CROSS APPLY (SELECT UPPER(name) as bigname) x
WHERE bigname = 'JOE'
-- PGSQL (not 100% sure of syntax)
SELECT bigname
FROM people
LEFT JOIN LATERAL (SELECT UPPER(name) as bigname) x on true
WHERE bigname = 'JOE'
The problem is this might have a negative impact on performance because it mucks with the query planner. I've seen everything from huge, complex, CROSS APPLYs have literally zero performance penalty to completely benign single-column renames tanking query performance 10x, so beware if you use this.