CODEWITHSUNDEEP
sqloracle-databaseora-00904
BlackShift
BlackShift

Reputation: 2406

How to reuse dynamic columns in an Oracle SQL statement?

I try to reuse some columns that I calculate dynamically in Oracle SQL, something like

SELECT
    A*2 AS P,
    P+5 AS Q
FROM tablename

Where 'tablename' has a column called 'A', but no other colums. This gives me an

ORA-00904: "P": invalid identifier

I know how to work around this by using a subquery like

SELECT P, P+5 AS Q
FROM ( SELECT A*2 AS P FROM tablename )

but I think this is kinda ugly. Furthermore I want to make the query somewhat more complex, e.g. reusing 'Q' as well, and I do not want to create yet another subquery.

Update: The reason I want to store the calculation of 'P' is that I want to make it more complex, and reuse 'P' multiple times. So I do not want to explicitly say 'A*2+5 AS Q', because that would quickly become to cumbersome as 'P' gets more complex.

There must be a good way to do this, any ideas?

Update: I should note that I'm not a DB-admin :(.

Update: A real world example, with a more concrete query. What I would like to do is:

SELECT 
    SL/SQRT(AB) AS ALPHA,
    5*LOG(10,ALPHA) AS B,
    2.5*LOG(10,1-EXP(-5/ALPHA)*(5/ALPHA+1)) AS D
    BS -2.74 + B + D AS BSA
FROM tablename

for now, I've written it out, which works, but is ugly:

SELECT
    SL/SQRT(AB) AS ALPHA,
    5*LOG(10,SL/SQRT(AB)) AS B,
    2.5*LOG(10,1-EXP(-5/(SL/SQRT(AB)))*(5/(SL/SQRT(AB))+1)) AS D
    BS -2.74 + 5*LOG(10,SL/SQRT(AB)) + 2.5*LOG(10,1-EXP(-5/(SL/SQRT(AB)))*((5/(SL/SQRT(AB)))+1)) AS BSA
FROM tablename

I could do all of this after receiving the data, but I thought, let's see how much I can let the database do. Also, I would like to select on 'BSA' as well (which I can do now with this query as a subquery/with-clause).

Update: OK, I think for now I finished with Cade Roux' and Dave Costa's solution. Albeit Pax' and Jens Schauder's solution would look better, but I can't use them since I'm not a DBA. Now I don't know who to mark as the best answer :).

WITH 
  A1 AS ( 
    SELECT A0.*, 
    SL/SQRT(AB) AS ALPHA
    FROM tablename A0
  ),
  A2 AS (
    SELECT A1.*, 
    5*LOG(10,ALPHA) AS B,
    2.5*LOG(10,1-EXP(-5/ALPHA)*((5/ALPHA)+1)) AS D
    FROM A1
  )
SELECT
  ALPHA, B, D, BS,
  BS -2.74 + B + D AS BSA
FROM A2

BTW, in case anyone is interested, SB is the 'surface brightness' of galaxies, for which B and D are correction terms.

Upvotes: 4

Views: 15115

Answers (6)

Galal Ouda
Galal Ouda

Reputation: 81

Alias reusing is common in Teradata however it can be confusion sometimes mainly when you alias a column name with a name that exists within the table/subquery and try to reuse it, the database will use the original column and not the one you aliased.

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425371

You can't.

If you don't want the subquery to be reevaluated, add a NO_MERGE hint for the subquery:

This subquery will be reevaluated in the nested loop (MERGE hint is used):

SELECT  /*+ LEADING(g) USE_NL(g, r) MERGE(g) */
        *
FROM    (
        SELECT  1
        FROM    dual
        UNION ALL
        SELECT  2
        FROM    dual
        ) r, 
        (
        SELECT  SYS_GUID() AS guid
        FROM    dual d
        ) g

---
33CA48C1AB6B4403808FB0219302CE43
711BB04F9AFC406ABAEF8A8F4CFA1266

This subquery will not be reevaluated in the nested loop (NO_MERGE hint is used):

SELECT  /*+ LEADING(g) USE_NL(g, r) NO_MERGE(g) */
        *
FROM    (
        SELECT  1
        FROM    dual
        UNION ALL
        SELECT  2
        FROM    dual
        ) r, 
        (
        SELECT  SYS_GUID() AS guid
        FROM    dual d
        ) g

------
7715C69698A243C0B379E68ABB55C088
7715C69698A243C0B379E68ABB55C088

In your case, just write:

SELECT  BS - 2.74 + d
FROM    (
        SELECT  t2.*, 2.5 * LOG(10, 1 - EXP(-5 / b)) * ((5 / A) + 1) AS d
        FROM    (
                SELECT  t1.*, 5 * LOG(10, alpha) AS b
                FROM    (
                        SELECT  /*+ NO_MERGE */ t.*,
                                SL/SQRT(AB) AS alpha
                        FROM    tablename t
                        ) t1
                ) t2
        ) t3

, which is more efficient (EXP and LOG are costly) and is much easier to debug.

Upvotes: 0

Cade Roux
Cade Roux

Reputation: 89661

We have this same problem in SQL Server (it's an ANSI problem). I believe it is intended to avoid confusing aliasing effects:

SELECT A * 2 AS A
    ,A * 3 AS B -- This is the original A, not the new A
FROM whatever

We work around it by stacking up common table expressions:

WITH A1 AS (
    SELECT A * 2 AS A
    FROM whatever
)
,A2 AS (
    SELECT A1.*
        ,A * 3 AS B
    FROM A1
)
,A3 AS (
    SELECT A2.*
        ,A + B AS X
    FROM A2
)
SELECT *
FROM A3

This is the most readable and maintable and followable version.

For UPDATEs, there is a deprecated SQL Server workaround using the column_name = notation, where you can reference a column which has been updated previously in the list. But this cannot be used in SELECTs.

I would hope that some ability to stack expressions (without using a scalar UDF) is added to ANSI SQL at some point in the future.

Upvotes: 3

Dave Costa
Dave Costa

Reputation: 48121

You might like this a little better than the inline view example you gave:

WITH inner_view AS ( SELECT A*2 AS P FROM tablename )
SELECT P, P+5 AS Q
FROM inner_view

It amounts to the same thing but it's a little clearer to read, I think.

If the computed column is something you will use in multiple columns, it may make sense to create a permanent view.

Oracle 11 (which I haven't used yet) has a virtual column feature that might be useful for you.

Upvotes: 0

Jens Schauder
Jens Schauder

Reputation: 81907

There is no direct way to do this in sql.

But you could define a function using PL/SQL. So your select would look like this

select 
    P(A), 
    Q(P(A)) 
from tablename

For P and Q this isn't (much) better then the original, but if the functions are complex, and don't have to many parameter, it might make your statement much more readable.

It also would allow you to test you functions independently from the sql statement, and any data.

Upvotes: 0

paxdiablo
paxdiablo

Reputation: 881463

I'm not sure you can do this (I've never seen it done) but you could work around it with:

SELECT
    A*2   AS P,
    A*2+5 AS Q
FROM tablename

That's certainly better than introducing a subquery.

The only other way I'd suggest is to create a view giving you the P/Q-type columns (using the formulae above) which would at least simplify the text of the query. Then you could just:

SELECT P, Q FROM viewintotablename

Upvotes: 0

Related Questions