Re: Subselect in the from clause

On 24 Jan 2002 05:46:34 -0800, clang_at_mediaprint.at (Christian Lang) wrote:
>Hi,
>who could explain the range of use of a subselect in the from clause.
>
>eg.
>
>select f1,f2,f3,f4
> from
> (select d1,d2,d3,d4 from table)
> ...

Not sure what you mean by "range of use". Just consider that the inner select is going to return a set of rows, and Oracle pretends that it's a table, with columns named as in the inner select.

So your above example would fail because the inner select doesn't have columns named f1, f2, f3, and f4. You can use column aliases to get around this:

select f1, f2, f3, f4
from
(select d1 f1, d2 f2, d3 f3, d4 f3 from table)

I often use this technique to alias columns that are complicated expressions:

select col1, col2, count(*) from
(
  select substr(col1, 1, 20) col1, '<p>' || paragraph col2 from table )
group by col1;

(Instead of repeating the string operations in GROUP BY, ORDER BY, etc. clauses.)

Subselects are also sometimes useful in joins, to trim one of the tables way down before using it in the join:

select one.*, two.col1 from

	(select * from table where <rare condition>) one,
	table2 two

Sometimes you might make the subselect return only a single value, just so you can get the same value available in every row of the result set:

for item in
(
  select col1, col2, col3, (select max(col1 + col2) from table) col4   from table
)
loop

-- item.col4 is the same value on every loop iteration
-- Getting it via subselect saves declaring a separate variable and
-- doing a SELECT INTO query.

John

--
Got an Oracle database question?
Try the search engine for the database docs at:
http://tahiti.oracle.com/