A selection of topics on IT and its application to finance.
Send me your comments, questions or suggestions by clicking here
Using an in-line view is essentially a way of replacing a table name in a FROM clause with the result of a sub-select statement. The results of the sub-select can then be used in all other parts of the main SELECT. If you think of it as a way of getting a second look at your data once its retireved then you'll get a pretty good idea as to what the're all about. As always it helps to see some examples:-
1) Supposing when we run this select statement we get the following result-set back.
select name, SUM(bonus) bonus
group by name
Now what we'd like to see also is the percentage of the total bonus pool each employee gets. Thats where the in-line view comes in:-
select name,100*sum(bouns)/total_bonus '% Total'
from pay_table, (SELECT SUM(BONUS) total_bonus FROM pay_table)
group by name,total_bonus
Name Bonus % Total
-------- -------- -----
TOM 500 35.72
DICK 670 47.85
HARRY 230 16.43
2) In this example we get the US dollar rate using a sub-select and use it to retrieve stocks priced in Euros whose capitalisation is over 3 billion US dollars.
select stock_name, nr_shares*price * (usd_rate /exchange_rate)
from currency c, stocktab s,(SELECT exchange_rate usd_rate
WHERE currency_code = '$US')
where c.currency_code = s.currency_code
and nr_shares*price * (usd_rate /exchange_rate) >= 3000000000
3) Retrieving data based on rownum ranges
If you try the following SELECT you will find it doesn't work
SELECT col1 from table1
WHERE rownum between 8 AND 15
No rows returned
However if you use an in-line view as in
SELECT rn, col1
(select rownum rn ,col1 from table1)
WHERE rn between 8 and 15
you'll find it works as expected