Thanks all. Not sure why but the initial instinct under client pressure was
that the query should have failed :) .
Taking a step back afterwards and doing a 10053 on a test query I was able
to observe what is happening with the query.
În mie., 21 sept. 2022 la 17:42, Mark W. Farnham <mwf@xxxxxxxx> a scris:
yes, this is like putting in all the parentheses in a mathematical
equation (even if you know the precedence rules, why make anyone [or thing,
such as a parser]) think about them when the purpose is to clearly
communicate your query?
select * from dba_users d where d.username in (select a.userid from
sys.aud$ a)
with the silly (in my opinion) use of userid instead of username in aud$)
holding what I think you probably want. I’m currently uncertain of all the
mismatches of column names in the dictionary that hold the same
de-normalized bits of information. I had that in grey matter cache for 4,
5, and 6, but gave up all hope circa 7.1. Sigh. Partition that.
I don’t understand why they didn’t use sys.user$.user# in sys.aud$, which
should be faster on which to perform a distinct, but I didn’t dig too
deeply.
One of the first things OAUG did was get Oracle to publish a concordance
of column name contents for the E-biz suite (the first ask of Larry, and he
delivered over the objections of Jeffrey in a famous “lunch speaker” versus
“dinner speaker” vignette. Such a concordance update for each release of
the database with an annotation of changes and new things would be quite
valuable!
Good luck,
mwf
*From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Mohamed Houri
*Sent:* Wednesday, September 21, 2022 7:58 AM
*To:* laurentiu.oprea06@xxxxxxxxx
*Cc:* ORACLE-L (oracle-l@xxxxxxxxxxxxx)
*Subject:* Re: why the query is not failing the syntax check
This is because of *column binding order in SQL*. We, first, bind columns
to tables in the subquery and, if not found, then to tables in the outer
query.
Best regards
Mohamed Houri
Le mer. 21 sept. 2022 à 13:51, Mohamed Houri <mohamed.houri@xxxxxxxxx> a
écrit :
Hello
You should always alias your subquery
SQL> select * from dba_users where username in (select distinct a.username
from sys.aud$ a);
select * from dba_users where username in (select distinct a.username from
sys.aud$ a)
*
ERROR at line 1:
ORA-00904: "A"."USERNAME": invalid identifier
Best regards
Mohamed
Le mer. 21 sept. 2022 à 13:42, Laurentiu Oprea <
laurentiu.oprea06@xxxxxxxxx> a écrit :
Hello everyone,
I have a strange one (at least for me), why the next query is not failing
because column username does not exist in aud$?
select * from dba_users where username in (select distinct username from
sys.aud$);
Thanks a lot.
--
Houri Mohamed
Oracle DBA-Developer-Performance & Tuning
Visit My - Blog <http://www.hourim.wordpress.com/>
Let's Connect -
<http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*
My Twitter <https://twitter.com/MohamedHouri> - MohamedHouri
<https://twitter.com/MohamedHouri>
--
Houri Mohamed
Oracle DBA-Developer-Performance & Tuning
Visit My - Blog <http://www.hourim.wordpress.com/>
Let's Connect -
<http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*
My Twitter <https://twitter.com/MohamedHouri> - MohamedHouri
<https://twitter.com/MohamedHouri>
