| What's Wrong with My View [message #374118] |
Fri, 25 May 2001 10:54  |
Curtis
Messages: 1
Registered: May 2001
|
Junior Member |
|
|
|
I created a simple view, pulling data from 3 different tables. The only time oracle will list the data from the view is when I use the asterick in my select statement. If I try to select a single column or any combination of columns I get a message that the name of the view is an 'invalid column name.' That is, if I type 'select log_number from corp_main_view', Oracle seems to be looking at corp_main_view as a column name even though it follows the word 'from'. Anyone who has encountered this kind of problem, please help.
|
|
|
|
|
|
| Re: What's Wrong with My View [message #374157 is a reply to message #374118] |
Mon, 28 May 2001 16:40  |
Sudhakar Atmakuru
Messages: 58
Registered: May 2001
|
Member |
|
|
I guess your CREATE VIEW statement did not include the alias/column name specification. Make sure that your CREATE VIEW statement includes the list of column (alias) names that you want to use with your select or any other statement for accessing the view. The SQL does not understand when you give a name that is not included in your view statement or differs from the actual/physical column names of the real tables.
Create the view with speicifying column/alias names. It should work.
For ex:
CREATE OR REPLACE VIEW CORP_MAIN_VIEW (LOG_NUMBER,LOG_NAME,LOG_INFO) AS SELECT A.UID_NUM, B.USER_NAME, C.USER_INFO FROM UTABLE1 A, UTABLE2 B, UTABLE3 C WHERE <criteria>;
In the above example, the view's columns, LOG_NUMBER,LOG_NAME,LOG_INFO represent UID_NUM of UTABLE1, USER_NAME of UTABLE2, and USER_INFO of UTABLE3 respectively. When you retrive different columns from these three tables, you dont even need to specify the aliases A,B, and C. But make sure you include the aliases/columns list (you want to use with your view) in CREATE VIEW statement. Be reminded, in SQL, you can create/access tables, columns or any kinda objects with any case of letters. No case sensitive is considered in ORACLE/SQL, unless it is expected to be so (in some cases only).
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
