Ewaver Ewaver - 3 months ago 28
SQL Question

How to use a calculated column to calculate another column in the same view

I am hoping you can help with this question. I am using Oracle SQL (SQL Developer for this view)...

If I have a table with the following columns:


  • ColumnA (Number)

  • ColumnB (Number)

  • ColumnC (Number)



In my view I have

Select
ColumnA,
ColumnB,
ColumnA + ColumnB As calccolumn1


Now at this point, I want to use calccolumn1
but I cannot just say...

Select
ColumnA,
ColumnB,
ColumnA + ColumnB As calccolumn1
calccolumn1 / ColumnC as calccolumn2


I am assuming I need some type of subquery..but this is where I need your help...
How would I word the query so that I can use calccolumn1 in another calculation within the same query? It might be an If then or a Case when, but bottomline it is some derived number.

Answer

You could use a nested query:

Select
  ColumnA,
  ColumnB,
  calccolumn1,
  calccolumn1 / ColumnC as calccolumn2
From (
  Select
    ColumnA,
    ColumnB,
    ColumnC,
    ColumnA + ColumnB As calccolumn1
  from t42
);

With a row with values 3, 4, 5 that gives:

   COLUMNA    COLUMNB CALCCOLUMN1 CALCCOLUMN2
---------- ---------- ----------- -----------
         3          4           7         1.4

You can also just repeat the first calculation, unless it's really doing something expensive (via a function call, say):

Select
  ColumnA,
  ColumnB,
  ColumnA + ColumnB As calccolumn1,
  (ColumnA + ColumnB) / ColumnC As calccolumn2
from t42; 

   COLUMNA    COLUMNB CALCCOLUMN1 CALCCOLUMN2
---------- ---------- ----------- -----------
         3          4           7         1.4 
Comments