ORA-00904: Invalid Identifier on Oracle query-CodePudding

Hi have the following query but when I am executing the query I am getting an error in oracle. The issue seems to be on the ORDER BY but I am not sure why there is an invalid identifier as my column is clearly present in the table. I am using sqldeveloper to execute the query.

ORA-00904: "FINALRESULTS"."TESTTIME": invalid identifier

WITH tests AS (
 SELECT 
   t.place, 
   t.stationnr, 
   t.result, 
   i.type, 
   t.time,  
 FROM 
   result_test t 
   INNER JOIN result_ident i ON t.place= i.place
   JOIN stat_testplacegroup pg on pg.place = t.place 
   INNER JOIN result_release r ON i.type= r.type
 WHERE 
   (
     t.testtime BETWEEN to_date(
       '2022-11-20T00:00:00', 'YYYY-MM-DD"T"HH24:MI:SS
'
     ) 
     AND to_date(
       '2022-11-26T00:00:00', 'YYYY-MM-DD"T"HH24:MI:SS'
     )
   )
), 
finalresults AS (
 SELECT 
   t.place "place", 
   t.stationnr "stationnr", 
   convres(t.result) "convres", 
   t.type "type", 
   t.testtime "testtime"
 FROM 
   tests t 
   INNER JOIN result_testecu_11 tecu ON tecu.testresid = t.testresid 
   INNER JOIN result_pathecu pecu ON pecu.ecuid = tecu.ecuid 
   INNER JOIN result_pathfunc func ON pecu.ecuid = func.ecuid 

 GROUP BY  
   t.place, 
   t.stationnr, 
   t.type, 
   t.testtime
   t.result
) 
SELECT 
 * 
FROM 
 finalresults 
ORDER BY 
 finalresults.testtime DESC FETCH FIRST 5 ROWS ONLY;

CodePudding user response：

You didn't post the whole query; that looks like unfinished CTE.

Anyway: I presume that culprit is your bad idea of enclosing identifiers into double quotes. testtime column is referenced twice, and this looks suspicious:

SELECT t.place "place",
       t.stationnr "stationnr",
       convres (t.result) "convres",
       t.TYPE "type",
       t.testtime "testtime"             --> here it is
  FROM tests t

As I said: it is unfinished, but - regarding what you said so far - there's the order by clause which probably looks like this:

select ... 
from finalresults
order by testtime      --> this

If you're referencing a column (or alias) whose name is enclosed into double quotes, you have to do that always:

order by "testtime"

In Oracle, that's usually bad idea. Avoid double quotes whenever possible (and that's pretty much always).

CodePudding user response：

Your first error is because you define the column alias:

finalresults AS (
 SELECT 
   t.place "place", 
   t.stationnr "stationnr", 
   convres(t.result) "convres", 
   t.type "type", 
   t.testtime "testtime"

and then refer to it using:

ORDER BY 
 finalresults.testtime DESC

Which does not use the same quoted identifier.

It should be:

ORDER BY 
 finalresults."testtime" DESC

However, then you will get onto the next errors as you define:

WITH tests AS (
 SELECT 
   t.place, 
   t.stationnr, 
   t.result, 
   i.type, 
   t.time,
 FROM
   ...

Which is invalid syntax as it has a trailing comma; but ignoring that, you then go on to use:

finalresults AS (
 SELECT 
   t.place "place", 
   t.stationnr "stationnr", 
   convres(t.result) "convres", 
   t.type "type", 
   t.testtime "testtime"
 FROM 
   tests t 
   ...

But your TESTS sub-query factoring clause never SELECTed a testtime column so you are going to get an error for an invalid identifier.

You then have more invalid syntax as:

 GROUP BY  
   t.place, 
   t.stationnr, 
   t.type, 
   t.testtime
   t.result

Is missing a comma between the final two identifiers.

What you probably want is to get rid of all the sub-query factoring clauses and just use a single SELECT, something like:

SELECT t.place, 
       t.stationnr, 
       convres(t.result) AS convres, 
       i.type, 
       t.testtime -- or should this be t.time?
FROM   result_test t 
       INNER JOIN result_ident i ON t.place= i.place
       INNER JOIN stat_testplacegroup pg on pg.place = t.place 
       INNER JOIN result_release r ON i.type= r.type
       INNER JOIN result_testecu_11 tecu ON tecu.testresid = t.testresid 
       INNER JOIN result_pathecu pecu ON pecu.ecuid = tecu.ecuid 
       INNER JOIN result_pathfunc func ON pecu.ecuid = func.ecuid 
WHERE  t.testtime BETWEEN DATE '2022-11-20' AND DATE '2022-11-26'
GROUP BY  
       t.place, 
       t.stationnr, 
       i.type, 
       t.testtime, -- or should this be t.time?
       t.result
ORDER BY t.testtime DESC
FETCH FIRST 5 ROWS ONLY;