ORA-00904: "A"."LAST_TIME": invalid identifier

Question

I am unable to find the exact problem with this simple code. Oracle developer showing invalid identifier. Help please

SELECT a.*,
USERNAME,LATITUDE, LONGITUDE, ACCURACY ,
(CASE   
WHEN a.Last_Time > '06:00' AND a.Last_Time < '09:00' THEN  '1'     
WHEN a.Last_Time > '09:00' AND a.Last_Time <'12:00' THEN  '2'
WHEN a.Last_Time > '12:00' AND a.Last_Time <'15:00' THEN  '3'
WHEN a.Last_Time > '15:00' AND a.Last_Time <'18:00' THEN  '4'
ELSE '5'   END ) "L_T"
FROM  
--   AP_IT.MDM_GPS_REPORT_DAILY;
(SELECT substr(LAST_LOCATED_TIME,12,5) as "Last_Time"

FROM GPS_REPORT_DAILY )a;

the derived table only has `last_time` column..hence you get the error. — Vamsi Prabhala, May 18 '17 at 13:41
@vkp The error is that there is not a `"LAST_TIME"` column in the sub-query, however the OP creates a `"Last_Time"` column... the error is actually caused because Oracle is case-sensitive and the OP is using quoted identifiers which tells Oracle not to change the case of the identifier (so `"LAST_TIME"` is not the same as `"Last_Time"`). — MT0, May 18 '17 at 14:18
See also [Oracle DB quote column names](http://stackoverflow.com/a/38328674/1509264). — MT0, May 18 '17 at 14:23

score 0 · Answer 1 · answered May 18 '17 at 13:44

0

Apart from the point highlighted by @vkp, you can remove the inner query and further simplify it. But remember that between is inclusive of the times. So if you want, replace between back with < and >.

SELECT a.*,
USERNAME,LATITUDE, LONGITUDE, ACCURACY ,
CASE  substr(LAST_LOCATED_TIME,12,5) 
WHEN between '06:00' AND  '09:00' THEN  '1'     
WHEN between '09:00' AND  '12:00' THEN  '2'
WHEN between '12:00' AND  '15:00' THEN  '3'
WHEN between '15:00' AND  '18:00' THEN  '4'
ELSE '5'   END as "L_T"
FROM  
GPS_REPORT_DAILY a;

answered May 18 '17 at 13:44

Utsav

7,914
2
17
38

The OP's question is `I am unable to find the exact problem with this simple code.` This does not identify what the issue is with the OP's code so does not answer the question. It also changes the logic from `06:00 < substr < 09:00` to `06:00 <= substr <= 09:00` which may introduce unintended side effects into the code. – MT0 May 18 '17 at 14:13
Agree on upper case part. That is the actual problem and should have mentioned in the answer. But looking as his query, I didn't feel he really needs a subquery. And I left usage of `between` to OP by mentioning it in the answer. – Utsav May 18 '17 at 14:49

MT0 · Answer 2 · 2017-05-18T14:20:02.167

Oracle is case-sensitive, however, the default behaviour is for Oracle to hide its case-sensitivity by converting all unquoted identifiers to upper case and it is only quoted identifiers where the case remains unchanged. So, in the inner query you have "Last_Time" which is quoted so Oracle will respect the case of the identifier, whereas the outer query uses Last_Time which is unquoted and oracle will convert it to LAST_TIME and this does not exist in the inner query.

You can either remove the quotation marks from the inner query:

SELECT a.*,
       CASE
        WHEN a.Last_Time > '06:00' AND a.Last_Time < '09:00' THEN  '1'     
        WHEN a.Last_Time > '09:00' AND a.Last_Time < '12:00' THEN  '2'
        WHEN a.Last_Time > '12:00' AND a.Last_Time < '15:00' THEN  '3'
        WHEN a.Last_Time > '15:00' AND a.Last_Time < '18:00' THEN  '4'
        ELSE '5'
      END AS L_T
FROM  (
  SELECT substr(LAST_LOCATED_TIME,12,5) as Last_Time
  FROM   GPS_REPORT_DAILY
)a;

Or use quotation marks for all the identifiers in the outer query:

SELECT a.*,
       CASE
        WHEN a."Last_Time" > '06:00' AND a."Last_Time" < '09:00' THEN  '1'     
        WHEN a."Last_Time" > '09:00' AND a."Last_Time" < '12:00' THEN  '2'
        WHEN a."Last_Time" > '12:00' AND a."Last_Time" < '15:00' THEN  '3'
        WHEN a."Last_Time" > '15:00' AND a."Last_Time" < '18:00' THEN  '4'
        ELSE '5'
      END AS L_T
FROM  (
  SELECT substr(LAST_LOCATED_TIME,12,5) as "Last_Time"
  FROM   GPS_REPORT_DAILY
)a;

Or, use upper-case in the quoted identifier:

SELECT a.*,
       CASE
        WHEN a.Last_Time > '06:00' AND a.Last_Time < '09:00' THEN  '1'     
        WHEN a.Last_Time > '09:00' AND a.Last_Time < '12:00' THEN  '2'
        WHEN a.Last_Time > '12:00' AND a.Last_Time < '15:00' THEN  '3'
        WHEN a.Last_Time > '15:00' AND a.Last_Time < '18:00' THEN  '4'
        ELSE '5'
      END AS L_T
FROM  (
  SELECT substr(LAST_LOCATED_TIME,12,5) as "LAST_TIME"
  FROM   GPS_REPORT_DAILY
)a;

(Also note that you are using non-inclusive ranges lower_bound < value < upper_bound so if the time is exactly 09:00 then it does not fall into either of the 1 or 2 cases.)

@VBAToddler If this or any other answer has solved your problem then you can click the tick icon (below the voting buttons, to the left of the question) to indicate that you have found an answer to your question. — MT0, May 19 '17 at 07:42

ORA-00904: "A"."LAST_TIME": invalid identifier

2 Answers2