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.
CodePudding user response:
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>
CodePudding user response:
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