(I'm asking this because I was reviewing this older question I asked and because I'm currently writing some queries and getting annoyed at how much I need to repeat myself).
In ISO-compliant SQL implementations, expressions and computed-columns in a
SELECT clause cannot be referenced by other expressions in the same query (i.e. not in other expressions in the same
SELECT, nor the same query's
GROUP BY and
HAVING clauses). The result is that queries that perform simple text-processing operations end-up unnecessarily verbose.
Consider a query that returns the first-substring delimited by angle-brackets
'<text goes here>' without causing runtime errors if the text is
'> like this <' or
SELECT CASE WHEN CHARINDEX( '<', textBlob ) > 0 AND CHARINDEX( '>', textBlob, CHARINDEX( '<', textBlob ) > CHARINDEX( '<', textBlob ) ) THEN SUBSTRING( textBlob, /*start:*/ CHARINDEX( '<', textBlob ), /*length:*/ CHARINDEX( '>', textBlob, CHARINDEX( '<', textBlob ) ) - CHARINDEX( '<', textBlob ) ) AS angleBracketText END FROM table -- Alternatively, move the CASE WHEN expression to the WHERE clause.
The only alternative which avoids any repetition of scalar function call-sites is to use derived-table inner-queries - which introduces repetition of column projections (which isn't as bad as scalar function repetition, but still tedious):
SELECT SUBSTRING( textBlob, openIdx, closeIdx - openIdx ) AS angleBracketText FROM ( SELECT textBlob, openIdx, closeIdx FROM ( SELECT textBlob, openIdx, CHARINDEX( '>', textBlob, openIdx ) AS closeIdx FROM ( SELECT textBlob, CHARINDEX( '<', textBlob ) AS openIdx FROM table ) AS q1 WHERE openIdx >= 0 ) AS q2 WHERE closeIdx > openIdx ) AS q3
Did Codd, et al. really design SQL to be used like this? I know that they intended for SQL to be a "natural language" (which was all the rage in the 1970s), which surely implies a degree of terseness and eliminating redundant logic (because natural-language has lots of self-references like "it", "this", "that", etc), so I doubt they really intended their language-rules to result in this kind of query being written...
So, at risk of asking an un-answerable question (as the original designers of SQL are away from the keyboard right now): Why did Codd, et al. prohibit intermediate scalars in SELECT - and how would they have recommended avoiding syntactic repetition like in the above queries?
(If I had my way (I wish...) I'd be able to express it like this...)
SELECT $openIdx = CHARINDEX( '<', textBlob ), $closeIdx = CASE WHEN $openIdx >= 0 THEN CHARINDEX( '>', textBlob, $openIdx ) END, CASE WHEN $openIdx >= 0 AND $closeIdx >= 0 THEN SUBSTRING( textBlob, $openIdx, $closeIdx - $openIdx ) END AS angleBracketText FROM table