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

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

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

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

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

Examples related to sql

Examples related to oracle