We can use Oracle SQL SELECT query to calculate or evaluate something as a completely new column. The data in the new column is either a constant or expression or derived from the columns of the table.
This page contains (Skip to topic)..
SELECT Query calculated column syntax
We can use a select query to calculate something out of the existing columns or using some expressions. The syntax for this is,
SELECT col1, col2, (col1+col2)/(col1*col2) as col3 FROM table1;
In this example, col1 and col2 are from table1. col3 is the calculated column. The value of the calculated column depends on the values of the columns it is using.
The expression (col1+col2)/(col1*col2) is given a name as col3. This is called the column alias.
You can also use constants to evaluate the value of the calculated column. For that, the syntax would be,
SELECT col1, col2, 100 as col3 FROM table1;
We have used 100 ( a constant ) as a value for all the rows of table1 and we gave the name as col3.
SELECT Query calculated column from another calculated column
This is an interesting thing to know. We calculated a value from some existing columns in the previous example. But, what to do if we want to use the derived column to derive another column further.
SELECT col1, col2, col1+col2 as col3, ((col1+col2)/col3)*100 as col4 FROM table1;--wrong usage
Now, the question is can we do that? Let us try with an example.
select job_id , min_salary , max_salary , max_salary - min_salary diff_salary , diff_salary / ( max_salary + min_salary ) * 100 derievd_from_derived from jobs; ORA-00904: "DIFF_SALARY": invalid identifier 00904. 00000 - "%s: invalid identifier" *Cause: *Action: Error at Line: 3 Column: 2
So, clearly, the Oracle could not understand the derived column at the same level. So, we have to find another way to do that. Here are a couple of ways,
Calculated column from another calculated column without using a subquery
The first method is pretty easy to guess and straightforward. All you need to do is, copy the derived column expression and use it as a whole in other derived from the derived column.
The syntax for the same is,
SELECT col1, col2, col1+col2 as col3, ((col1+col2)/(col3 expression i.e. col1+col2))*100 as col4 FROM table1;
Here is the same example as taken above.
select job_id , min_salary , max_salary , max_salary - min_salary diff_salary , (max_salary - min_salary) / ( max_salary + min_salary ) * 100 derievd_from_derived from jobs;
The query runs just fine.
This is not the all. We can get the same result with the help of a subquery.
Calculated column from another calculated column using the subquery
We can compute the 1st derived column in the inner query and compute the 2nd derived column using the 1st derived column.
The syntax for the same is shown below.
SELECT col1 ,col2 ,col3 * col1 + col2 as col4 FROM ( SELECT col2 - col1 as col3 FROM table1 );
Here is a simple example explaining the above concept.
select col3 * 100 col4 from ( select 2 as col3 from dual );
The query in the above point can be rewritten as shown below.
select job_id , min_salary , max_salary , diff_salary / ( max_salary + min_salary ) * 100 derievd_from_derived from ( select job_id , max_salary , min_salary , max_salary - min_salary diff_salary from jobs );
The result is the same as shown in the screenshot above. However, you can see that this requires the knowledge of the Oracle subquery.