Getting ORA-00904: invalid identifier for a numeric column although it is present in the database

Question

I tried to run below query on oracle database

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')

That gives the below error:

ORA-00904: "LIMITTBL"."LSL": invalid identifier

904. 00000 - "%s: invalid identifier"

The DDL of LIMITTBL table is

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

Answer 1

This is too much code to put into a comment; if columns really existed, your code would have worked:

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>

However, as I commented - if you enclosed column names into double quotes and used lower/mixed letter case, query would have failed; see line #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

If that's the case, you'll have to use double quotes and the same letter case every time you reference that column (lines #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>