How to reuse dynamic columns in an Oracle SQL statement?

Question

I am trying to reuse some columns that I compute dynamically in Oracle SQL, something like

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

Where "tablename" has a column named "A" but no other columns. It gives me

 ORA-00904: "P": invalid identifier 

I know how to get around this using a subquery like

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

but I think this is ugly. In addition, I want to make the request a little more complicated, for example. reusing "Q" and I do not want to create another subquery.

Update. The reason I want to keep the calculation of “P” is because I want to make it more complex and reuse “P” several times. Therefore, I do not want to explicitly say “A * 2 + 5 AS Q” because it would quickly become cumbersome as “P” becomes more complex.

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

Update: I have to note that I am not DB-admin: (.

---

Update: An example of the real world with a more specific request. I would like to do the following:

 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 wrote this, which works, but 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 this after receiving the data, but I thought I would see how much I can allow the database. In addition, I would also like to select “BSA” (what can I do with this request now as a subquery / c-sentence).

---

Update: OK, I think I’m done now with the solution of Cade Roux and Dave Costa. Although Pax and Jens Schauder solutions would look better, I cannot use them since I am not a database administrator. Now I do not know who should mark 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 someone is interested, SB is the “surface brightness” of galaxies for which B and D are correction members.

Answer 1

We have the same problem in SQL Server (this is an ANSI problem). I believe that it is intended to avoid confusing overlay effects:

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

We go around it by adding 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 basic and next version.

For UPDATE, there is an obsolete SQL Server workaround using the column_name = notation column, where you can reference a column that was previously updated in the list. But this cannot be used in SELECT.

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

Answer 2

I'm not sure you can do this (I have never seen this), but you can get around it with:

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

This, of course, is better than introducing a subquery.

The only other way that I propose is to create a view giving you P / Q columns (using the formulas above) that will at least simplify the query text. Then you could simply:

 SELECT P, Q FROM viewintotablename 

Answer 3

You can not.

If you do not want to override the subquery, add the NO_MERGE hint for the subquery:

This subquery will be reevaluated in a nested loop (using MERGE ):

 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 a nested loop (using NO_MERGE ):

 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, simply 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 expensive) and much easier to debug.

Answer 4

There is no direct way to do this in sql.

But you can define a function using PL / SQL. So your choice will look like this:

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

For P and Q, this is not much better than the original, but if the functions are complex and do not have a large number of parameters, this can make your statement more readable.

It will also allow you to test your functions independently of the sql statement and any data.

Answer 5

You might like this a little better than the example of the inline view that you specified:

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

This is the same, but in my opinion it is a little clearer to read.

If the computed column is what you will use in multiple columns, it might make sense to create a constant view.

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

Answer 6

Reusing aliases is common in Teradata, however it can sometimes be confusing, mainly when you create an alias for the column name with the name that exists in the table / subquery and try to reuse it, the database will use the original column, and not the one you used as an alias.