Skip to Main Content
  • Questions
  • Select query on two calculated fields, one depends on other calculated field

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, krishna.

Asked: February 16, 2018 - 3:52 pm UTC

Last updated: February 19, 2018 - 9:43 am UTC

Version: 11

Viewed 1000+ times

You Asked

SELECT
A,
B,
A+B AS C,
CASE C (I don't want to use again A+B here its lengthy query)
------
-----
------
END As D
FROM TABLE

So here C is a calculated field, I want use result of C to calculate other field D. How can I do this without having performance issue.

and Connor said...

An inline view will do the trick:

SQL> create table t ( a int, b int, c int, d int );

Table created.

SQL>
SQL> insert into t values (1,2,3,4);

1 row created.

SQL>
SQL> select
  2    a,
  3    b,
  4    c,
  5    a+b-c+d/2+10-sqrt(c) as ugh
  6  from t;

         A          B          C        UGH
---------- ---------- ---------- ----------
         1          2          3 10.2679492

1 row selected.

SQL>
SQL> select
  2    a,
  3    b,
  4    c,
  5    ugh,
  6    case when ugh > 0 then 'Y' else 'N' end more_stuff
  7  from
  8    (
  9        select
 10          a,
 11          b,
 12          c,
 13          a+b-c+d/2+10-sqrt(c) as ugh
 14  from t
 15  );

         A          B          C        UGH M
---------- ---------- ---------- ---------- -
         1          2          3 10.2679492 Y

1 row selected.



Is this answer out of date? If it is, please let us know via a Comment

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.