RE: why the query is not failing the syntax check
Date: Wed, 21 Sep 2022 10:41:17 -0400
Message-ID: <01ad01d8cdc8$356a1e90$a03e5bb0$_at_rsiz.com>
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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mohamed Houri
Sent: Wednesday, September 21, 2022 7:58 AM
To: laurentiu.oprea06_at_gmail.com
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_at_gmail.com> 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)
Best regards
Mohamed
Le mer. 21 sept. 2022 à 13:42, Laurentiu Oprea <laurentiu.oprea06_at_gmail.com> 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 <https://twitter.com/MohamedHouri> Twitter - 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 <https://twitter.com/MohamedHouri> Twitter - MohamedHouri <https://twitter.com/MohamedHouri>
Cc: ORACLE-L (oracle-l_at_freelists.org)
Subject: Re: why the query is not failing the syntax check
*
ERROR at line 1:
ORA-00904: "A"."USERNAME": invalid identifier
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Sep 21 2022 - 16:41:17 CEST