elmama
​A selection of topics on IT and its application to finance. 
​Send me your comments, questions ​or suggestions by clicking
h​ere


In-line views 


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
from pay_table
group by name

Name              Bonus
--------               ------
TOM                500
DICK               670
HARRY           230


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
                                                 FROM currency
                                                 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
FROM
(select rownum rn ,col1 from table1)
WHERE rn between 8 and 15



you'll find it works as expected