CODEWITHSUNDEEP
sqloracle-database
Randy
Randy

Reputation: 803

Error with a SQL statement

What I am trying to do is create a view that selects empno, ename, deptno renames the columns empno to Employee_ID, ename to Employee, Deptno to Department_ID Any ideas to why I have these problems? 

SQL> create view DEPT20 AS
2  select empno,ename,deptno (employee_id, employee, Department_ID) from emp
3  where deptno = 20
4  with check option constraint emp_dept_20;
select empno,ename,deptno (employee_id, employee, Department_ID) from emp
                   *
ERROR at line 2:
ORA-00904: "DEPTNO": invalid identifier

I know its there so why do I get the error?

SQL> select empno, ename, deptno from emp;

 EMPNO ENAME          DEPTNO
 ---------- ---------- ----------
  7839 KING               10
  7698 BLAKE              30
  7782 CLARK              10
  7566 JONES              20
  7654 MARTIN             30
  7499 ALLEN              30
  7844 TURNER             30
  7900 JAMES              30
  7521 WARD               30
  7902 FORD               20
  7369 SMITH              20

 EMPNO ENAME          DEPTNO
 ---------- ---------- ----------
  7788 SCOTT              20
  7876 ADAMS              20
  7934 MILLER             10
  8888 Stuttle            40

 15 rows selected.

 SQL>

Upvotes: 0

Views: 123

Answers (3)

mjStallinger
mjStallinger

Reputation: 192

Maybe it helps, if you try this one:

SELECT 
  empno as employee_id,
  ename as employee,
  deptno as Department_ID
FROM emp ...

Upvotes: 2

jaypal singh
jaypal singh

Reputation: 77095

why don't you try like this - 

... select empno as employee_id, ename as employee, deptno as Department_ID 
from emp where Department_ID = 20 ...

Upvotes: 1

Doozer Blake
Doozer Blake

Reputation: 7797

It looks like you're trying to rename the columns. I think you need to do it like the following

select empno as employee_id, ename as employee,deptno as department_id from emp

Upvotes: 2

Related Questions