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

User · Answer

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

User · Answer

You have to include the expression for your calculated column   SELECT   ColumnA    ColumnB    ColumnA   ColumnB AS calccolumn1    ColumnA   ColumnB    ColumnC AS calccolumn2

User · Answer

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

User · Answer

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

User · 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

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

Examples related to sql

Examples related to oracle