advertisements
_____________________________________________________________________________________________________________________
Normally we cannot create a view with a dummy null column. The column defined in the view must be present in the base table. If the column is not present in the base tables Oracle will throw ORA-00904: "CARNAME": invalid identifier error. Here I am going to present a demo for creating a view with Null column using a function.
I have two tables dept and emp1. I wanted to create a view with name, department name, location, and a dummy column carname.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select * from emp1;
NAME AGE LAST_NAME DEPT
-------------------- ---------- -------------------- ----------
Toad 52 10
John 32 20
Trying to create a view without base column and it is erroring out.
SQL> create or replace view emp_dept as
2 select Name, Dname, Loc, CarName
3 from emp1, dept
4 where emp1.dept=dept.deptno;
select Name, Dname, Loc, CarName
*
ERROR at line 2:
ORA-00904: "CARNAME": invalid identifier
Create a function carname as the name with return value as null.
SQL> create or replace function carname return varchar2 is
2 begin
3 return null;
4 end;
5 /
Function created.
1 create or replace view emp_dept as
2 select Name, Dname, Loc, CarName
3 from emp1, dept
4* where emp1.dept=dept.deptno
SQL> /
View created.
1* select * from emp_dept
SQL> /
NAME DNAME LOC CARNAME
-------------------- -------------- ------------- ----------
Toad ACCOUNTING NEW YORK
John RESEARCH DALLAS
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment