Re: inline views

From: Daniel W. Fink <optimaldba_at_yahoo.com>
Date: Fri, 06 Jun 2003 09:55:35 -0700
Message-ID: <F001.005AC50E.20030606093459@fatcity.com>

Here is one case where an inline view is adventageous. PL/SQL may work better, but the requirement is a single SQL statement.

Display a single employee from each department. The employee displayed should be a random selection and not just the first one retrieved from the table.

(extra columns added for explanatory purposes)

SQL> select e1.deptno,

 2        e1.empno,
 3        e1.dept_rank,
 4        e2.max_emps,
 5        s1.rand_num,
 6        mod(s1.rand_num, e2.max_emps)+1 filter_condition
 7  from (select deptno,
 8              empno,
 9              rank() over (partition by deptno order by empno) as 
dept_rank
10       from emp) e1,
11      (select deptno,
12              count(deptno) max_emps
13       from emp
14       group by deptno) e2,
15      (select to_char(sysdate, 'ss') rand_num
16       from dual) s1

17 where e1.deptno is not null
18 and e1.deptno = e2.deptno
19 and e1.dept_rank = mod(s1.rand_num, e2.max_emps)+1;

   DEPTNO EMPNO DEPT_RANK MAX_EMPS RA FILTER_CONDITION
---------- ---------- ---------- ---------- -- ----------------

       10       7934          3          3 26                3
       20       7566          2          5 26                2
       30       7654          3          6 26                3

--

Daniel W. Fink
http://www.optimaldba.com

VIVEK_SHARMA wrote:

Where are they advantageous to use & where not ?

Thanks  

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Daniel W. Fink
 INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Fri Jun 06 2003 - 11:55:35 CDT