Why does code output ORA-00904 on code that I used a syntax verifier for?

Question

So im trying to run a command in Oracel Apex to select a colum were primary key of tabel one and foreing key of tabel 2 are the same.

`select name from COMP_EMPL where COMP_EMPL.employee_id = MANAGER."employee_id";`

When i run it the error:

`ORA-00904: "MANAGER"."employee_id": invalid identifier apears`

The tabel names are corect and the column names are corect so its not a spelling isue. The tabels are populated and coresponding values for the two keys exist.

Error

COMP_EMPL table

MANAGER table

Sorry the photots are as code but it would not let me post the question otherwise (smth about bad formating)

I have tried removing the "" from the column names but its solved nothing i also tried renaming the column but nothing changed same error with diferent name also the same error apears werever i use MANAGER."employee_id"  so its not an isue with that specific comand.

Answer 1

First of all, code you posted is invalid. It is a join you need; you can't reference the MANAGER table in WHERE clause just because. Correct syntax is

select c."name"
from comp_empl c join manager m on m.employee_id = c."employee_id"

For example, with some sample data:

SQL> with
  2  comp_empl ("employee_id", "name") as
  3    (select 1, 'Little' from dual),
  4  manager ("manager_id", employee_id) as
  5    (select 100, 1 from dual)

Query you'd use:

  6  select c."name"
  7  from comp_empl c join manager m on m.employee_id = c."employee_id"
  8  /

name
------
Little

SQL>

Note double quotes I used for identifiers. They are necessary because you chose to create tables using double quotes. It means that you have to enclose them into double quotes EVERY TIME you reference them. If you used mixed letter case, you'd have to match letter case as well.

Shortly: it is a bad idea to use double quotes when working with Oracle. By default, Oracle stores names (tables, procedures, columns, ...) in uppercase, but lets you reference them using any letter case you want unless you used double quotes.

If you look at e.g. MANAGER table, you'll see that manager_id is created in lower case (so you have to enclose it into double quotes and write lower case), while EMPLOYEE_ID is written in upper case (so you can reference it using upper case, or enclose them into double quotes but also with upper case).

On the other hand, all columns in COMP_EMPL table were created in lower case (which means with double quotes).

---

If I were you, I'd drop both tables and create new ones as e.g.

create table manager
  (manager_id    number,
   employee_id   number
  );

Then you can use all this:

select * from MAnaGer where EMPLOYEE_id = 1
or
select EMPLOYEe_Id from manager where MANAGER_ID = 100
or ...

Using sample data from beginning of this post:

SQL> with
  2  comp_empl (employee_ID, NAme) as
  3    (select 1, 'Little' from dual),
  4  manager (MANAGER_id, EmPlOyEe_ID) as
  5    (select 100, 1 from dual)
  6  select c.name
  7  from comp_empl c join manager m on m.employee_id = c.employee_id;

NAME
------
Little

SQL>

Answer 2

The error appears because there is no "MANAGER" table in the FROM clause. If you reference a table in the where clause, it needs to exist in the FROM clause. In your case a simple join would do, there where clause is not needed. This statement does the same as what you're trying to achieve:

select name 
  from COMP_EMPL C
       JOIN MANAGER M ON C.employee_id = M.employee_id