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

Question

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 1

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