Geek Answers Handbook

Forced external SELECT fails if the internal SELECT contains an invalid identifier

If

SELECT ID FROM T2

failed with the following message:

Error: ORA-00904: "Identifier": invalid identifier

why not

 SELECT * FROM T1 WHERE ID IN ( SELECT ID FROM T2 )

necessary ? (it returns all records from T1)

Is it possible to change this default behavior?
(running the same request, but getting an error instead of all rows)

I have:

  • T1 with id in column
  • T2 with ID2 as a column (T2 does not contain an identifier)

but let's say that I use SELECT ID FROM T2 (see example above) instead of SELECT ID2 FROM T2 by mistake. In this situation, nothing bad happens, because I use SELECT ... IN SELECT ... , but it can cause serious damage if it is replaced by DELETE ... IN SELECT ...

+1
source share
2 answers

The behavior is explained in this question .

But you also asked: “Is it possible to change this default behavior? (By running the same request, but getting an error instead of all the lines)” and expanded on this in the comment with “I want to force a failure for the same request, and not change the request, to get the desired result. "

No, changing behavior is not possible. He does what the documentation says he should do:

Oracle allows unqualified columns in a subquery by looking at the tables named in the subquery and then in the tables specified in the parent expression.

You cannot force it to stop looking at the parent statement and resolve an unqualified alias inside the subquery. You will need to modify the request to make it a mistake. There is no reason not to qualify your identifiers and many reasons why you should, including the fact that this may prevent you from masking errors in your code.

+4
source

Yes.

 SELECT * FROM T1 WHERE ID IN ( SELECT T2.ID FROM T2 )
+3
source

More articles:


All Articles