Selecting just one table from Oracle view [closed]

Question

I am creating a view to ease my manual selecting because I only want to fetch data from DB where it is meeting certain criteria which are present in several tables, so it is containing a lot of joins.

Problem is that usually I would like to just look for one table's data, not everything even though data is fetched through a view. What would be the best way of implementing this with Oracle?

In practice, something like this I would want to do

CREATE VIEW emp_emp( e1_ename, e2_empno, e2_deptno )
     SELECT e1.ename, e2.empno, e2.deptno
     FROM emp e1, emp_add e2
     WHERE e1.empno = e2.empno;

I would want to be able to say SELECT e2.* FROM emp_emp;

To my current knowledge I would have to write all fields related to table e2 by hand.

I have been thinking a solution where I could actually use USER_TAB_COLS table to dynamically create a list of columns i would want to select. So there could be a wrapper which would create SELECT E2_EMPNO, E2_DEPTNO FROM emp_emp; automatically if i would call for instance a function with parameter list of all tables I want to fetch.

Answer 1

You can't select columns from the view that aren't exposed in the view definition. If your view only includes three columns in the SELECT list, you can only reference those three columns in queries against the view.

You can't use table aliases that are defined in the view in queries against the view. You cannot, therefore, reference the e2 alias when you are querying the view.

In your view definition, you may be able to use something like e2.* to specify a list of columns to SELECT. But that either requires that every column in the SELECT list has a unique name, that you alias the columns to avoid name collisions, or that you explicitly list the column names in your view definition. Since you are doing a self-join, the only way that you could ensure that every column in the SELECT list has a unique name would be if you were only selecting data from e1 or from e2 in the view definition,

CREATE VIEW emp_emp 
AS 
SELECT e2.*
  FROM emp e1, 
       emp e2 
 WHERE e1.empno = e2.empno;

Of course, that is not particularly useful. You can alias the columns you are selecting from e1 to avoid name collisions.

CREATE VIEW emp_emp 
AS 
SELECT e1.ename e1_ename,
       e2.*
  FROM emp e1, 
       emp e2 
 WHERE e1.empno = e2.empno;

Or you can explicitly list the columns in your view definition

CREATE VIEW emp_emp( e1_ename, e2_empno, ... )
AS 
SELECT e1.ename,
       e2.*
  FROM emp e1, 
       emp e2 
 WHERE e1.empno = e2.empno;

Normally, listing out the columns you want to select, particularly doing it once when you are defining a view, isn't a big deal and isn't something you should seek to avoid. It's just part of coding. Normally, explicitly listing the column names is the appropriate way to define the view.

Be aware that, unlike with a standalone SQL query, Oracle automatically expands the * when you define the view. So even if you define the view with an e2.*

CREATE VIEW emp_emp 
AS 
SELECT e1.ename e1_ename,
       e2.*
  FROM emp e1, 
       emp e2 
 WHERE e1.empno = e2.empno;

if you add a new column to e2, that new column will not be added to the view.