101

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.

Kiquenet
  • 14,494
  • 35
  • 148
  • 243
Ewaver
  • 1,121
  • 5
  • 13
  • 22
  • 1
    Is there a reason not to just do `(ColumnA + ColumnB) / ColumnC As calccolumn2`? – Alex Poole Oct 04 '13 at 15:32
  • 7
    I agree...in this simplified example..that would work fine. I was trying to get to the concept of how I would format a simplied example to apply to a more complex one...so if ColumnA was a complex Case When statement and ColumnB was also...then if I want to combined them to produce calccolumn1 and later use to calccolumn2. Would I simply just put the entire Case state for Column A in () and the same for B and add them? – Ewaver Oct 04 '13 at 15:43
  • 1
    @AlexPoole Having complex expression on the same level could be handy. Single definition is easier to maintain than copying it over multiple places. It is doable using [`CROSS APPLY approach`](https://stackoverflow.com/a/52024045/5070879) – Lukasz Szozda Aug 26 '18 at 07:37
  • Of course, the question just didn't show a complex expression really. I was going to say cross apply wasn't available when the question was asked, but 12c had been out a few months by then, I just wasn't aware of it... The subquery approach doesn't repeat it either though. – Alex Poole Aug 26 '18 at 09:12
  • @AlexPoole There is nothing wrong with inline view/CTE approach. I just wanted to show that it is possible to have complex expression at the same query level as alternative. – Lukasz Szozda Aug 26 '18 at 10:20

5 Answers5

75

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
Alex Poole
  • 183,384
  • 11
  • 179
  • 318
31

In Sql Server

You can do this using cross apply

Select
  ColumnA,
  ColumnB,
  c.calccolumn1 As calccolumn1,
  c.calccolumn1 / ColumnC As calccolumn2
from t42
cross apply (select (ColumnA + ColumnB) as calccolumn1) as c
Manoj
  • 4,951
  • 2
  • 30
  • 56
16

If you want to refer to calculated column on the "same query level" then you could use CROSS APPLY(Oracle 12c):

--Sample data:
CREATE TABLE tab(ColumnA NUMBER(10,2),ColumnB NUMBER(10,2),ColumnC NUMBER(10,2));

INSERT INTO tab(ColumnA, ColumnB, ColumnC) VALUES (2, 10, 2);
INSERT INTO tab(ColumnA, ColumnB, ColumnC) VALUES (3, 15, 6);
INSERT INTO tab(ColumnA, ColumnB, ColumnC) VALUES (7, 14, 3);
COMMIT;

Query:

SELECT
  ColumnA,
  ColumnB,
  sub.calccolumn1,
  sub.calccolumn1 / ColumnC AS calccolumn2
FROM tab t
CROSS APPLY (SELECT t.ColumnA + t.ColumnB AS calccolumn1 FROM dual) sub;

DBFiddle Demo

Please note that expression from CROSS APPLY/OUTER APPLY is available in other clauses too:

SELECT
  ColumnA,
  ColumnB,
  sub.calccolumn1,
  sub.calccolumn1 / ColumnC AS calccolumn2
FROM tab t
CROSS APPLY (SELECT t.ColumnA + t.ColumnB AS calccolumn1 FROM dual) sub
WHERE sub.calccolumn1 = 12;
-- GROUP BY ...
-- ORDER BY ...;

This approach allows to avoid wrapping entire query with outerquery or copy/paste same expression in multiple places(with complex one it could be hard to maintain).

Related article: The SQL Language’s Most Missing Feature

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
8

You have to include the expression for your calculated column:

SELECT  
ColumnA,  
ColumnB,  
ColumnA + ColumnB AS calccolumn1  
(ColumnA + ColumnB) / ColumnC AS calccolumn2
Uli Köhler
  • 13,012
  • 16
  • 70
  • 120
swirlingsara
  • 105
  • 1
  • 4
3

In SQL Server

 You can do this using With CTE

WITH common_table_expression (Transact-SQL)

CREATE TABLE tab(ColumnA DECIMAL(10,2), ColumnB DECIMAL(10,2), ColumnC DECIMAL(10,2))

INSERT INTO tab(ColumnA, ColumnB, ColumnC) VALUES (2, 10, 2),(3, 15, 6),(7, 14, 3)

WITH tab_CTE (ColumnA, ColumnB, ColumnC,calccolumn1)  
AS  
(  
Select
    ColumnA,
    ColumnB,
    ColumnC,
    ColumnA + ColumnB As calccolumn1
  from tab
)  

SELECT
  ColumnA,
  ColumnB,
  calccolumn1,
  calccolumn1 / ColumnC AS calccolumn2
FROM  tab_CTE

DBFiddle Demo

Vahid Heydarinezhad
  • 116
  • 4