Tutorial :How to reuse dynamic columns in an Oracle SQL statement?



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
Previous
Next Post »