ORA-00904：此实例中的无效标识符 [英] ORA-00904: invalid identifier in this instance

问题描述

这是我的解决方案，我想用3个表中的所有信息显示薪水，当我把工资结束时，无论我尝试什么，我还是错了。有人帮我解决我不知道如何显示只有15行的工资。

  SELECT Bld.id，C.code，M。 FIRST_NAME，M.LAST_NAME，Bld.Address，M.ADDRESS，D.DOB，S.SALARY 
来自HW1_PERSON M 
内部连接HW1_BUILDING Bld 
 ON M.id = Bld.id 
 INNER JOIN HW1_PERSON M 
 ON Bld.id = M.id 
 INNER JOIN HW1_PERSON M 
 ON M.id = Bld.id 
 Inner JOIN HW1_BUILDING Bld 
 ON Bld.id = M.id 
 INNER JOIN HW1_BUILDING C 
 ON M.id = C.id 
 INNER JOIN HW1_PERSON D 
 ON M.id = D.id 
 UNION ALL 
 SELECT Bld.id，C.code，M.FIRST_NAME，M.LAST_NAME，Bld.Address，M.ADDRESS，D.DOB，S.SALARY FROM HW1_STAFF S 
其中S. SALARY = NULL 
; 
  

---

这里是表

  CREATE TABLE HW1_PERSON（
 ID INT NOT NULL，
 FIRST_NAME VARCHAR（32）NOT NULL，
 LAST_NAME VARCHAR 32）NOT NULL，
 DOB DATE，
 ADDRESS VARCHAR2（32），
 CONSTRAINT PK_HW1_PERSON_ID PRIMARY KEY（ID））; 
 
 CREATE TABLE HW1_STAFF（
 SALARY DOUBLE PRECISION NOT NULL，
 START_DATE DATE NOT NULL，
 END_DATE DATE DEFAULT NULL，
 EMP_NUM VARCHAR（5）NOT NULL UNIQUE，
 PERSON_ID INT NOT NULL，
 CONSTRAINT PK_HW1_STAFF PRIMARY KEY（PERSON_ID），
 CONSTRAINT FK_STAFF_PERSON FOREIGN KEY（PERSON_ID）参考HW1_PERSON（ID））; 
 
 CREATE TABLE HW1_BUILDING（
 ID INT NOT NULL，
 CODE VARCHAR2（5）NOT NULL UNIQUE，
 NAME VARCHAR2（32）NOT NULL，
 ADDRESS VARCHAR2（32），
 MANAGER_ID INT，
 CONSTRAINT PK_HW1_BUILDING PRIMARY KEY（ID），
 CONSTRAINT FK_BUILDING_STAFF FOREIGN KEY（MANAGER_ID）REFERENCES HW1_STAFF（PERSON_ID））; 
 
 CREATE TABLE HW1_EMPLOYEE（
 ID INT NOT NULL，
 PERSON_ID INT，
 BUILDING_ID INT，
 CONSTRAINT PK_HW1_EMPLOYEE PRIMARY KEY（PERSON_ID，BUILDING_ID），
 CONSTRAINT FK_STAFF_ID FOREIGN KEY（PERSON_ID）参考HW1_STAFF（PERSON_ID），
 CONSTRAINT FK_BUILDING_ID FOREIGN KEY（BUILDING_ID）参考HW1_BUILDING（ID））; 
  

---

数据示例

  INSERT INTO hw1_person（id，first_name，last_name，dob，address）VALUES（1，'fname11'，'lname11'，to_date（'7/27/1990 '，'MM / DD / YYYY'），'527第15 Av CT'）; 
 INSERT INTO hw1_person（id，first_name，last_name，dob，address）VALUES（2，'fname12'，'lname12'，to_date（'12 / 5/1966'，'MM / DD / YYYY' 254第十一章）; 
 
 
 INSERT INTO hw1_staff（person_id，emp_num，start_date，end_date，salary）VALUES（1，'WZIAE'，to_date（'7/1/1965'，'MM / DD / YYYY '），null，70000）; 
 INSERT INTO hw1_staff（person_id，emp_num，start_date，end_date，salary）VALUES（2，'EWEMU'，to_date（'11 / 18/1980'，'MM / DD / YYYY'），to_date（'12 / 26/1970'，'MM / DD / YYYY'），80000）; 
 INSERT INTO hw1_staff（person_id，emp_num，start_date，end_date，salary）VALUES（25，'00001'，to_date（'5/21/1994'，'MM / DD / YYYY'），null，0）; 
 INSERT INTO hw1_staff（person_id，emp_num，start_date，end_date，salary）VALUES（26，'00002'，to_date（'7/19/1985'，'MM / DD / YYYY'），null，0）; 
  

解决方案

首先，你在这里做了一些不必要的连接。 / p>

您从HW1_PERSON写了

  M 
内部连接HW1_BUILDING Bld 
 ON M.id = Bld.id 
 INNER JOIN HW1_PERSON M 
 ON Bld.id = M.id 
 INNER JOIN HW1_PERSON M 
 ON M.id = Bld.id 
 Inner JOIN HW1_BUILDING Bld 
 ON Bld.id = M.id 
 INNER JOIN HW1_BUILDING C 
 ON M.id = C.id 
 INNER JOIN HW1_PERSON D 
 ON M.id = D.id 
  

但是，如果你写了这个，那么这将是足够的

 从HW1_PERSON M 
内部连接HW1_BUILDING Bld 
 ON M.id = Bld .id 
 INNER JOIN HW1_BUILDING C 
 ON M.id = C.id 
  

此外，您同时使用 M 和 D 作为同一个表 HW1_PERSON - 执行查询时会导致错误

其次，如评论中所述r第一个查询不包含 HW1_STAFF 中的 S.SALARY 列。从你的表结构，我想你可以通过在第一个查询中得到这个列。

  SELECT Bld.id，C .code，M.FIRST_NAME，M.LAST_NAME，Bld.Address，M.ADDRESS，M.DOB，S.SALARY 
来自HW1_PERSON M 
内部连接HW1_BUILDING Bld 
 ON M.id = Bld.id 
 INNER JOIN HW1_BUILDING C 
 ON M.id = C.id 
 INNER JOIN HW1_STAFF S 
 ON S.PERSON_ID = M.id 
  

此外，在第二个查询中，您在<$ c中搜索了 NULL $ c> S.SALARY ，但根据 HW1_STAFF 表的定义，该列不会为空。所以，在第二个查询中，你不会得到任何结果。也许你应该把这个查询改成这样的一个例子。

  SELECT Bld.id，C.code，M.FIRST_NAME，M。 LAST_NAME，Bld.Address，M.ADDRESS，D.DOB，S.SALARY FROM HW1_STAFF S 
其中S.END_DATE = NULL 
  

然后，整个查询将看起来像这样

  SELECT Bld.id，C。代码，M.FIRST_NAME，M.LAST_NAME，Bld.Address，M.ADDRESS，M.DOB，S.SALARY 
来自HW1_PERSON M 
内部连接HW1_BUILDING Bld 
 ON M.id = Bld .id 
 INNER JOIN HW1_BUILDING C 
 ON M.id = C.id 
 INNER JOIN HW1_STAFF S 
 ON S.PERSON_ID = M.id 
 UNION ALL 
 SELECT Bld.id，C.code，M.FIRST_NAME，M.LAST_NAME，Bld.Address，M.ADDRESS，M.DOB，S.SALARY FROM HW1_STAFF S 
其中S.END_DATE = NULL 
; 
  

Here is my solution, I want to display all the information in 3 tables with salary, when I put salary at then end, no matter what I tried,I still got wrong. Someone help me fix. I don't know how to display the salary with only 15 rows.

SELECT Bld.id,C.code,M.FIRST_NAME,M.LAST_NAME,Bld.Address,M.ADDRESS,D.DOB,S.SALARY
    from HW1_PERSON M
    inner join HW1_BUILDING Bld
    ON M.id = Bld.id
    INNER JOIN HW1_PERSON M 
    ON Bld.id = M.id
    INNER JOIN HW1_PERSON M 
    ON M.id = Bld.id
    Inner JOIN HW1_BUILDING Bld
    ON Bld.id = M.id
    INNER JOIN HW1_BUILDING C
    ON M.id = C.id
    INNER JOIN HW1_PERSON D
    ON M.id = D.id
    UNION ALL
    SELECT Bld.id,C.code,M.FIRST_NAME,M.LAST_NAME,Bld.Address,M.ADDRESS,D.DOB,S.SALARY FROM HW1_STAFF S
    where S.SALARY = NULL
    ;

---

Here are the tables

CREATE TABLE HW1_PERSON (
     ID INT NOT NULL,
     FIRST_NAME VARCHAR(32) NOT NULL,
     LAST_NAME VARCHAR(32) NOT NULL,
     DOB DATE,
     ADDRESS VARCHAR2(32),
     CONSTRAINT PK_HW1_PERSON_ID PRIMARY KEY (ID));

CREATE TABLE HW1_STAFF (
     SALARY DOUBLE PRECISION NOT NULL,
     START_DATE DATE NOT NULL,
     END_DATE DATE DEFAULT NULL,
     EMP_NUM VARCHAR(5) NOT NULL UNIQUE,
     PERSON_ID INT NOT NULL,
     CONSTRAINT PK_HW1_STAFF PRIMARY KEY (PERSON_ID),
     CONSTRAINT FK_STAFF_PERSON FOREIGN KEY(PERSON_ID) REFERENCES HW1_PERSON(ID));

CREATE TABLE HW1_BUILDING (
     ID INT NOT NULL,
     CODE VARCHAR2(5) NOT NULL UNIQUE,
     NAME VARCHAR2(32) NOT NULL,
     ADDRESS VARCHAR2(32),
     MANAGER_ID INT,
     CONSTRAINT PK_HW1_BUILDING PRIMARY KEY (ID),
     CONSTRAINT FK_BUILDING_STAFF FOREIGN KEY(MANAGER_ID) REFERENCES HW1_STAFF(PERSON_ID));

CREATE TABLE HW1_EMPLOYEE (
     ID INT NOT NULL,
     PERSON_ID INT,
     BUILDING_ID INT,
     CONSTRAINT PK_HW1_EMPLOYEE PRIMARY KEY (PERSON_ID,BUILDING_ID),
     CONSTRAINT FK_STAFF_ID FOREIGN KEY(PERSON_ID) REFERENCES HW1_STAFF(PERSON_ID),
     CONSTRAINT FK_BUILDING_ID FOREIGN KEY(BUILDING_ID) REFERENCES HW1_BUILDING(ID));

---

DATA EXAMPLE

INSERT INTO hw1_person (id, first_name, last_name, dob, address) VALUES (1, 'fname11', 'lname11', to_date('7/27/1990', 'MM/DD/YYYY'), '527 15th Av CT');
INSERT INTO hw1_person (id, first_name, last_name, dob, address) VALUES (2, 'fname12', 'lname12', to_date('12/5/1966', 'MM/DD/YYYY'), '254 11th St CT');


INSERT INTO hw1_staff (person_id, emp_num, start_date, end_date, salary) VALUES (1, 'WZIAE', to_date('7/1/1965', 'MM/DD/YYYY'), null,70000);
INSERT INTO hw1_staff (person_id, emp_num, start_date, end_date, salary) VALUES (2, 'EWEMU', to_date('11/18/1980', 'MM/DD/YYYY'), to_date('12/26/1970', 'MM/DD/YYYY'),80000);
INSERT INTO hw1_staff (person_id, emp_num, start_date, end_date, salary) VALUES (25, '00001', to_date('5/21/1994', 'MM/DD/YYYY'), null,0);
INSERT INTO hw1_staff (person_id, emp_num, start_date, end_date, salary) VALUES (26, '00002', to_date('7/19/1985', 'MM/DD/YYYY'), null,0);

解决方案

Firstly, you have made some unnecessary joins here.

You wrote

from HW1_PERSON M
inner join HW1_BUILDING Bld
ON M.id = Bld.id
INNER JOIN HW1_PERSON M 
ON Bld.id = M.id
INNER JOIN HW1_PERSON M 
ON M.id = Bld.id
Inner JOIN HW1_BUILDING Bld
ON Bld.id = M.id
INNER JOIN HW1_BUILDING C
ON M.id = C.id
INNER JOIN HW1_PERSON D
ON M.id = D.id

but, if you had written this, then it would be enough

from HW1_PERSON M
inner join HW1_BUILDING Bld
ON M.id = Bld.id
INNER JOIN HW1_BUILDING C
ON M.id = C.id

Moreover, you have used both M and D as aliases of the same table HW1_PERSON - which causes error while executing the query

Secondly, as mentioned in comments, your first query doesn't contain S.SALARY column from HW1_STAFF. From your table structures, I think you can get the column by doing this in your first query.

SELECT Bld.id,C.code,M.FIRST_NAME,M.LAST_NAME,Bld.Address,M.ADDRESS,M.DOB,S.SALARY
from HW1_PERSON M
inner join HW1_BUILDING Bld
ON M.id = Bld.id
INNER JOIN HW1_BUILDING C
ON M.id = C.id
INNER JOIN HW1_STAFF S
ON S.PERSON_ID = M.id

Moreover, in your second query, you searched for NULL in S.SALARY, but by definition of your HW1_STAFF table, that column will not be null. So, in the second query, you will not get any result. Perhaps you should change that query - to something like this

SELECT Bld.id,C.code,M.FIRST_NAME,M.LAST_NAME,Bld.Address,M.ADDRESS,D.DOB,S.SALARY FROM HW1_STAFF S
where S.END_DATE = NULL

Then, the whole query will look something like this

SELECT Bld.id,C.code,M.FIRST_NAME,M.LAST_NAME,Bld.Address,M.ADDRESS,M.DOB,S.SALARY
from HW1_PERSON M
inner join HW1_BUILDING Bld
ON M.id = Bld.id
INNER JOIN HW1_BUILDING C
ON M.id = C.id
INNER JOIN HW1_STAFF S
ON S.PERSON_ID = M.id
UNION ALL
SELECT Bld.id,C.code,M.FIRST_NAME,M.LAST_NAME,Bld.Address,M.ADDRESS,M.DOB,S.SALARY FROM HW1_STAFF S
where S.END_DATE = NULL
;

这篇关于ORA-00904：此实例中的无效标识符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！