Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

closed as off topic by Jack Douglas Aug 31 '12 at 8:02

Questions on Database Administrators Stack Exchange are expected to relate to database administration within the scope defined by the community. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about reopening questions here.If this question can be reworded to fit the rules in the help center, please edit the question.

    
Aliases are just for inside the view - they are not visible outside so yes, you must specify the columns you actually want. I'm closing this as it is too basic for this site. –  Jack Douglas Aug 31 '12 at 8:02
    
That is something I would want to avoid because I am writing ad-hoc queries and view is containing a lot of columns I would want to fetch. –  Speksi Aug 31 '12 at 8:35
2  
then create another view with just the columns you want on top of the first? –  Jack Douglas Aug 31 '12 at 9:00
1  
yes you can do this with dynamic SQL, no you will not solve more problems than you create :) –  Jack Douglas Aug 31 '12 at 9:01
add comment

1 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.

share|improve this answer
    
Please ping me in The Heap if you think I've made the wrong call on closing Justin - I don't want your effort to feel unappreciated! –  Jack Douglas Aug 31 '12 at 8:03
    
Thank you for your response. It didn't actually response my original question of selecting a view, you did explain how to tackle problems in creating a view. I've updated my problem description a bit. –  Speksi Aug 31 '12 at 8:20
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.