Forum Stats

  • 3,837,036 Users
  • 2,262,221 Discussions
  • 7,900,185 Comments

Discussions

Ability to define column variables just like common table expressions

Lukas Eder
Lukas Eder Member Posts: 126 Bronze Badge
edited Jul 22, 2016 3:20AM in Database Ideas - Ideas

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.

Lukas EderJeffrey Kempilya.maskoffThomas Teske-OracleFranck PachotMatheus BoesingApexBinectriebtrentnohupGbenga AjakayeNiels HeckerWilliam RobertsonberxThorsten Kettner
17 votes

Active · Last Updated

Comments

  • William Robertson
    William Robertson Member Posts: 9,568 Bronze Crown
    edited Jul 22, 2016 12:21PM

    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
    Mike Kutz Member Posts: 6,195 Silver Crown

    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
    Lukas Eder Member Posts: 126 Bronze Badge

    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
    Lukas Eder Member Posts: 126 Bronze Badge

    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

    ApexBine
  • Matheus Boesing
    Matheus Boesing Member Posts: 46 Blue Ribbon

    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
    William Robertson Member Posts: 9,568 Bronze Crown
    edited Jul 26, 2016 5:20AM

    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
    Lukas Eder Member Posts: 126 Bronze Badge

    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
    William Robertson Member Posts: 9,568 Bronze Crown

    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

    ApexBineLukas Eder
  • Racer I.
    Racer I. Member Posts: 113
    edited Feb 15, 2019 8:55AM

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

  • Sven W.
    Sven W. Member Posts: 10,537 Gold Crown
    edited Nov 29, 2019 11:11AM

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