Since we are dealing with this kind of queries I would like to add that a where clause like:
where field in (select another_field from another_table where something) works also in the query parameter of exp.This allows you to export rows from a table based on values in another table.
I used it a few days ago to copy a referential integrity subset from production to development, since they are on different sub nets and there is no direct connection between them.
Yechiel Adar Israel On 29/03/2011 20:28, Kenneth Naim wrote:
This has to do with correlated queries, where the inner query can access the outer query. I've never liked it as it seems out of scope, but I've learned to deal with it and on very rare occasions have need them. These types of queries were more popular prior to 8i where a lot of the sql restriction on inline views were lifted. As was previously mentioned using table aliases is a good/best practice prevents this bug from happening.Ken*From:*oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Mark Strickland*Sent:* Tuesday, March 29, 2011 2:08 PM *To:* Harel Safra *Cc:* oracle-l@xxxxxxxxxxxxx*Subject:* Re: A SQL bug/feature? Non-existent column in select of in-list subquery returns rows instead of ORA-Yeah, I understand that but it doesn't seem correct to me. The inner query queries dba_tables, not dba_users.Note that the same behavior exists in SQL Server.On Tue, Mar 29, 2011 at 10:41 AM, Harel Safra <harel.safra@xxxxxxxxx <mailto:harel.safra@xxxxxxxxx>> wrote:Username in the inner query referenced dba_users since it can't find such a column in dba_tables.Harel Safra Sent from my phone.On Mar 29, 2011 6:50 PM, "Mark Strickland" <strickland.mark@xxxxxxxxx <mailto:strickland.mark@xxxxxxxxx>> wrote:> 11.2.0.1 and 11.2.0.2 on OEL. > > Try "select * from dba_users where username in (select username from> dba_tables)". It returns all the rows from dba_users instead of returning > an error. Perhaps this is consistent with SQL 92 but I don't see how. Did> Dr. Codd really intend this? If it is expected behavior, I'm willing to> except the shunning and ridicule (even from Mladen). One of our developers > discovered this yesterday with application tables and I confirmed it with an> equivalent query of data dictionary tables and opened an SR. Can someone > confirm this in 10g? > > Mark Strickland > Seattle, WA ------------------------------------------------------------------------ Checked by AVG - www.avg.com <http://www.avg.com> Version: 10.0.1204 / Virus Database: 1498/3537 - Release Date: 03/29/11 ------------------------------------------------------------------------ Checked by AVG - www.avg.com <http://www.avg.com> Version: 10.0.1204 / Virus Database: 1498/3537 - Release Date: 03/29/11
