Re: why the query is not failing the syntax check

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_at_rsiz.com> 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_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
> *Cc:* ORACLE-L (oracle-l_at_freelists.org)
> *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_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)
> *
> ERROR at line 1:
> ORA-00904: "A"."USERNAME": invalid identifier
>
>
>
>
>
> 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 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>
>

--
http://www.freelists.org/webpage/oracle-l