Creating view in oracle database

[Aileen_L]

Dear all,
I am getting some error during creation of views and the statement that I used to create is given,tell me what is the problem in the statement :

Create view Banktransact_view  as
SELECT C.Emp_Id, C.Supplier, C.Account_No, C.Voch_N,C.Voch_Date,        
    C.ExpMonth, C.BillingMonth,C.Amount, C.Notes, b.grade
     FROM CASH_tras C, billinghr b
where CASH_tras.emp_id=billinghr.emp_id;

And occur some following error :

Error: ORA-00904: "BILLINGHR"."EMP_ID": invalid identifier
00904. 00000 - "%s: invalid identifier"

Please tell me what would be the solution of this error.

Thanks.

[Praetor]

Issues with creation of views :

If you are getting the error due to creation of views,then make sure the columns and tables that you are passing with create statement that should be exist in the database.Otherwise,you will be prompted as unavailability error.

The error that you have mentioned above that is absolutely wrong. The definition statement is wrong.You are using alias for the database tables with theFROM clause and you are using table name in the condition(CASH_tras.emp_id=billinghr.emp_id).

You need to use the alias name in the WHERE clause also.

[Katty]

The views in oracle :

The View is an logical entity of the database,The database is being used to keep secure the database tables and work as a window for database table.
You can perform query, update, insert into, and delete from views just as what you can do with standard database tables.

The view not only represent the data of single table but from different tables of database also.There are two types of views are being used :

1- Simple views
2- Complex views (It collects data from different tables with the functions property and can't update ).

[kelfro]

Creating views:

You can create the views using following statement which is given below :

CREATE OR REPLACE VIEW <view_name> AS
SELECT <column_name>
FROM <table_name>;

The CREATE OR REPLACE clause indicates that the create a new view and if another view is created with this name,just replace it from the database.The example is given below, The name of the table is "Employees" and I am going to create a view on this table.

Create or replace Emp_view as 
select from emp_id,emp_name,city from Employees;

[Zecho]

The Updatable views :

The view which is created without using database functions are called Updatable views.When we include some functions and all with the view those are called complex view.The following things which is not acceptable by the updatable views are :

• Set Operators Like INTERSECT, MINUS, UNION, UNION ALL
• DISTINCT clause
• Group Aggregate Functions Like AVG, COUNT, MAX, MIN, SUM, etc.
• GROUP BY Clause
• ORDER BY Clause
• CONNECT BY Clause
• START WITH Clause
• Collection Expression In A Select List
• Subquery In A Select List
• Join Query

[Reegan]

Dropping the views:

You can drop views but need to Grant to drop it.There are some privileges regarding view are create view ,create any view,drop any view,under any view.You can't drop any view without the drop privilege which is granted by object owner or database owner only.

To drop the view,you need to use this statement :

SQL>DROP VIEW <view_name>;