oracle 获取ORA-00904：数据库中存在数值列的无效标识符

0dxa2lsx 于 2023-10-16 发布在 Oracle

关注(0)|答案(1)|浏览(88)

我试着在oracle数据库上运行下面的查询

select distinct LABEL,EVENT_DATETIME,USL,LSL from 
(select eventTbl.LABEL,eventTbl.EVENT_DATETIME,limitTbl.USL,limitTbl.LSL from EVENT_TABLE eventTbl
          INNER JOIN LIMITS_TABLE limitTbl on limitTbl.start_date <= eventTbl.EVENT_DATETIME
          AND limitTbl.end_date >= eventTbl.EVENT_DATETIME where eventTbl.plant_id = 'plant1')

这会导致以下错误：
ORA-00904：“LIMITTBL”.“LSL”：无效标识符

00000 -“%s：无效标识符”
LIMITTBL表格的格式为

create table LIMITS_TABLE(
plant_id varchar2(80),
start_date date,
end_date date,
USL number(11,5),
LSL number(11,5)
);

oracle

来源：https://stackoverflow.com/questions/77270398/getting-ora-00904-invalid-identifier-for-a-numeric-column-although-it-is-presen

1条答案

按热度按时间

nwlls2ji1#

这是太多的代码放在一个评论;如果列真的存在，你的代码就可以工作了：

SQL> WITH
  2     event_table AS (SELECT 'plant1' plant_id, 'A' label, SYSDATE event_datetime FROM DUAL),
  3     limits_table
  4     AS
  5        (SELECT 1 usl, 2 lsl, SYSDATE start_date, SYSDATE + 2 end_date FROM DUAL)
  6  SELECT DISTINCT LABEL,
  7                  EVENT_DATETIME,
  8                  USL,
  9                  LSL
 10    FROM (
 11            SELECT eventTbl.LABEL,
 12                   eventTbl.EVENT_DATETIME,
 13                   limitTbl.USL,
 14                   limitTbl.LSL
 15              FROM EVENT_TABLE  eventTbl
 16                   INNER JOIN LIMITS_TABLE limitTbl
 17                      ON     limitTbl.start_date <= eventTbl.EVENT_DATETIME
 18                         AND limitTbl.end_date >= eventTbl.EVENT_DATETIME
 19             WHERE eventTbl.plant_id = 'plant1'
 20         );

LABEL EVENT_DATE        USL        LSL
----- ---------- ---------- ----------
A     11.10.2023          1          2

SQL>

然而，正如我所评论的-如果你把列名括在双引号里，并使用小写/混合大小写，查询就会失败;见第5行：

SQL> WITH
  2     event_table AS (SELECT 'plant1' plant_id, 'A' label, SYSDATE event_datetime FROM DUAL),
  3     limits_table
  4     AS
  5        (SELECT 1 "usl", 2 "LsL", SYSDATE start_date, SYSDATE + 2 end_date FROM DUAL)
  6  SELECT DISTINCT LABEL,
  7                  EVENT_DATETIME,
  8                  USL,
  9                  LSL
 10    FROM (
 11            SELECT eventTbl.LABEL,
 12                   eventTbl.EVENT_DATETIME,
 13                   limitTbl.USL,
 14                   limitTbl.LSL
 15              FROM EVENT_TABLE  eventTbl
 16                   INNER JOIN LIMITS_TABLE limitTbl
 17                      ON     limitTbl.start_date <= eventTbl.EVENT_DATETIME
 18                         AND limitTbl.end_date >= eventTbl.EVENT_DATETIME
 19             WHERE eventTbl.plant_id = 'plant1'
 20         );
                 limitTbl.LSL
                 *
ERROR at line 14:
ORA-00904: "LIMITTBL"."LSL": invalid identifier

如果是这样的话，你必须在每次引用该列时使用双引号和相同的字母大小写（第7，8，13，14行）：

SQL> WITH
  2     event_table AS (SELECT 'plant1' plant_id, 'A' label, SYSDATE event_datetime FROM DUAL),
  3     limits_table
  4     AS
  5        (SELECT 1 "usl", 2 "LsL", SYSDATE start_date, SYSDATE + 2 end_date FROM DUAL)
  6  SELECT DISTINCT LABEL,
  7                  EVENT_DATETIME,
  8                  "usl",
  9                  "LsL"
 10    FROM (
 11            SELECT eventTbl.LABEL,
 12                   eventTbl.EVENT_DATETIME,
 13                   limitTbl."usl",
 14                   limitTbl."LsL"
 15              FROM EVENT_TABLE  eventTbl
 16                   INNER JOIN LIMITS_TABLE limitTbl
 17                      ON     limitTbl.start_date <= eventTbl.EVENT_DATETIME
 18                         AND limitTbl.end_date >= eventTbl.EVENT_DATETIME
 19             WHERE eventTbl.plant_id = 'plant1'
 20         );

LABEL EVENT_DATE        usl        LsL
----- ---------- ---------- ----------
A     11.10.2023          1          2

SQL>

赞(0）回复(0）举报 2023-10-16

我来回答

oracle 获取ORA-00904：数据库中存在数值列的无效标识符

1条答案

相关问题

热门标签

最新问答