###

Question:

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.

###

Solution:1

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.

###

Solution:2

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 `

###

Solution:3

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.

###

Solution:4

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.

###

Solution:5

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.

**Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com**

EmoticonEmoticon