Ability to define column variables just like common table expressions

Lukas Eder · Jul 22, 2016 3:20AM

Common table expressions (a.k.a. subquery factoring) is a very useful tool to compose complex SQL queries. They're the only real way to declare variables in the SQL language. I often wish that something similar were possible for column expressions as well, and in fact many users do so. For instance, a lot of people struggle when they have to repeat complex expressions in SELECT and GROUP BY. These things can be solved, of course, using common table expressions or derived tables, but what if there was a more convenient syntax? I have proposed this in the past on the jOOQ blog:

https://blog.jooq.org/2014/01/06/the-sql-languages-most-missing-feature/

The idea is that there would need to be a WITH clause (or some other keyword) right after the table reference, i.e. after the FROM clause. So, when today, we need to write:

<code class="sql keyword" style="font-size: 1em !important; font-family: Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace !important; font-weight: bold !important; margin: 0 !important; padding: 0 !important; color: #006699 !important; background-position: initial !important;">SELECT first_name || ' ' || last_name<br/>FROM customers

WHERE first_name || ' ' || last_name LIKE 'L% E%'

GROUP BY first_name || ' ' || last_name

ORDER BY first_name || ' ' || last_name

... with the improved syntax, we could write instead:

<code class="sql keyword" style="font-size: 1em !important; font-family: Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace !important; font-weight: bold !important; margin: 0 !important; padding: 0 !important; color: #006699 !important; background-position: initial !important;">SELECT name

<code class="sql keyword" style="font-size: 1em !important; font-family: Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace !important; font-weight: bold !important; margin: 0 !important; padding: 0 !important; color: #006699 !important; background-position: initial !important;">FROM customers

WITH name AS first_name || ' ' || last_name

WHERE name LIKE 'L% E%'

GROUP BY name

ORDER BY name

The whole feature would just be syntax sugar. The expression could be inlined and expanded immediately into the surrounding SQL statement, without any impact on performance. It just helps users re-use complex expressions across SELECT, WHERE, GROUP BY, HAVING, ORDER BY and other clauses.

I think this would be a real killer addition, also to the SQL standard, not just to Oracle SQL.

William Robertson · Jul 22, 2016 11:52AM

In your example, you could just write group by first_name, last_name - but I see what you mean.

Perhaps it would be simpler if we could just refer to the column alias in the group by clause, the same way we can refer to it in the order by clause, as in https://community.oracle.com/ideas/3441.

Currently we can do this:

select s.sequence_name, s.last_number, s.min_value, s.cache_size
     , round((s.last_number - (s.min_value -1)) / s.cache_size) as increments
from  user_sequences s
order by increments;

but not this:

select round((s.last_number - (s.min_value -1)) / s.cache_size) as increments
     , count(*)
from  user_sequences s
group by increments;

(To be fair, I suppose that wouldn't help your where clause example. It's probably unreasonable to expect SQL to allow us to refer to a column alias in the same select list.)

Mike Kutz · Jul 22, 2016 12:02PM

What you suggest looks more like a macro or MS-SQL syntax.

With 12c, you can definea function in the SQL.

(syntax might be wrong)

WITH

function calc_name( p_first_name in varchar2, p_last_name in varchar2 )

return varchar2

deterministic

as

begin

return p_first_name || ' ' || p_last_name;

end;

SELECT calc_name( first_name, last_name ) name

FROM CUSTOMERS

group by first_name, last_name -- function is deterministic, you don't need to group by the result of the function.

order by name -- ORDER BY can use column aliases

;

MK

Lukas Eder · Jul 22, 2016 6:01PM

In your example, you could just write group by first_name, last_name - but I see what you mean.
Perhaps it would be simpler if we could just refer to the column alias in the group by clause, the same way we can refer to it in the order by clause, as in https://community.oracle.com/ideas/3441.
Currently we can do this:
select s.sequence_name, s.last_number, s.min_value, s.cache_size
     , round((s.last_number - (s.min_value -1)) / s.cache_size) as increments
from  user_sequences s
order by increments;
but not this:
select round((s.last_number - (s.min_value -1)) / s.cache_size) as increments
     , count(*)
from  user_sequences s
group by increments;
(To be fair, I suppose that wouldn't help your where clause example. It's probably unreasonable to expect SQL to allow us to refer to a column alias in the same select list.)

You can do the exercise. Is "GROUP BY first_name, last_name" really the same thing as "GROUP BY first_name || ' ' || last_name"? Let's ask Steve Jones a.k.a. Mr Johnson and Steve a.k.a. Mr Jones Johnson

My suggestion deliberately puts the column expression definition after FROM, because if we were allowed to reuse aliases from SELECT, we'd be creating tons of confusion such as:

SELECT allows for using aggregate functions, which aren't allowed in WHERE
SELECT allows for using window functions, which aren't allowed in WHERE, GROUP BY, HAVING
SELECT means I actually project the column. But perhaps I only want an expression to be reusable between WHERE and GROUP BY
SELECT DISTINCT doesn't work correctly if I have to add excess columns just to be able to reuse them

There are many more. All databases that shoehorn this idea into SELECT alias reusability get this wrong. This idea can really only be implemented by adding a new clause right after FROM

Lukas Eder · Jul 22, 2016 6:04PM

What you suggest looks more like a macro or MS-SQL syntax.
With 12c, you can definea function in the SQL.
(syntax might be wrong)
WITH
function calc_name( p_first_name in varchar2, p_last_name in varchar2 )
return varchar2
deterministic
as
begin
return p_first_name || ' ' || p_last_name;
end;
SELECT calc_name( first_name, last_name ) name
FROM CUSTOMERS
group by first_name, last_name -- function is deterministic, you don't need to group by the result of the function.
order by name -- ORDER BY can use column aliases
;
MK

Indeed, that helps greatly for reuse, apart from the fact that the function is a fence for the optimiser. I wish there was a SQL-only solution for the same.

Btw: Your GROUP BY expression is not the same as mine. Imagine two people called Steve Jones a.k.a. Mr Johnson and Steve a.k.a. Mr Jones Johnson

Matheus Boesing · Jul 22, 2016 9:49PM

It actually can be done with Subquery Factoring, as described here: https://oracle-base.com/articles/misc/with-clause

But I think I understand you suggestion as a simplification of this syntax, right? This way, I don't believe it's a bad idea... It's always good to have a easier and quicker syntax to make things.
So, I don't see it as a "problem solving", but as a "syntax improvement". Up voting.

William Robertson · Jul 23, 2016 3:55AM

You can do the exercise. Is "GROUP BY first_name, last_name" really the same thing as "GROUP BY first_name || ' ' || last_name"? Let's ask Steve Jones a.k.a. Mr Johnson and Steve a.k.a. Mr Jones Johnson
My suggestion deliberately puts the column expression definition after FROM, because if we were allowed to reuse aliases from SELECT, we'd be creating tons of confusion such as:
SELECT allows for using aggregate functions, which aren't allowed in WHERE
SELECT allows for using window functions, which aren't allowed in WHERE, GROUP BY, HAVING
SELECT means I actually project the column. But perhaps I only want an expression to be reusable between WHERE and GROUP BY
SELECT DISTINCT doesn't work correctly if I have to add excess columns just to be able to reuse them
There are many more. All databases that shoehorn this idea into SELECT alias reusability get this wrong. This idea can really only be implemented by adding a new clause right after FROM

Excellent points, Lukas. I'll need to think about GROUP BY X || Y vs GROUP BY X, Y.

Could you clarify with an example?

Lukas Eder · Jul 24, 2016 1:29PM

Excellent points, Lukas. I'll need to think about GROUP BY X || Y vs GROUP BY X, Y.
Could you clarify with an example?

Sure. Here's an example that shows that the results would be different:

https://livesql.oracle.com/apex/livesql/file/content_DMHICSQ2UN56Z1HB74X8F4326.html

William Robertson · Jul 25, 2016 4:57AM

Sure. Here's an example that shows that the results would be different:
https://livesql.oracle.com/apex/livesql/file/content_DMHICSQ2UN56Z1HB74X8F4326.html

Ah, good point. Nice livesql too

Racer I. · Feb 15, 2019 8:51AM

Probably next to impossible but maybe all these (and more) can be smooshed together somehow?

Sven W. · Nov 29, 2019 11:10AM

SQL Macros are coming in 20c. At a first glance it looks as if this idea is implemented using SQL macros.

Forum Stats

Quick Links

Categories

Ability to define column variables just like common table expressions

Active · Last Updated Jul 22, 2016 3:20AM

Comments

On-Premise Applications

On-Premise Infrastructure

Industry Applications

Leadership Forums

On-Premise Applications

On-Premise Infrastructure

Industry Applications

Leadership Forums

Forum Stats

Categories

Ability to define column variables just like common table expressions

Active · Last Updated Jul 22, 2016 3:20AM

Comments