db-torque-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Remke Rutgers <Remke.Rutg...@brightalley.nl>
Subject RE: IN clause capable of adding a subquery?
Date Tue, 17 Dec 2002 08:54:51 GMT
Hi John,

Thanks for your reaction.

I agree that usage of such a clause would be limited to db's that support
subqueries. If I am not mistaken this use is specified in ANSI 92 SQL, so it
would not be a very vendor-specific enhancement.

As for the limit of an IN list in Oracle: I think it is 256. (But I must
admit we are using an almost prehistoric 7.3.4 installation here)

Btw, I found an interesting thread here on IN clauses and Oracle:
http://ccm.redhat.com/bboard-archive/webdb/000bmQ.html

It suggests using EXISTS instead of IN for performance reasons.
Is there a way to build such a construct using Torque? (Other than manually
specifying the whole EXISTS clause?) I realize that using EXISTS instead of
IN may improve performance in Oracle, but might degrade performance in other
databases. I also realize that EXISTS is probably not supported by all
databases.

Remke


-----Oorspronkelijk bericht-----
Van: John McNally [mailto:jmcnally@collab.net]
Verzonden: maandag 16 december 2002 16:52
Aan: Turbine Torque Developers List
Onderwerp: Re: IN clause capable of adding a subquery?


Sounds useful.  There are ways to add such a complex criteria, but not
very elegant and they pretty much boil down to constructing the string
by hand.  A patch would be welcome, though usage of such a construct
would limit the code to use in db's that support subqueries.  BTW, what
is the limit in oracle for the number of items in an IN list? 

john mcnally

On Mon, 2002-12-16 at 05:36, Remke Rutgers wrote:
> Hi developers,
> 
> As far as I can tell, it is not possible to construct an IN clause with a
> subquery using Torque.
> I would like to be able to construct a query of the following form:
> (just a simple example)
> 
> SELECT * FROM employees
> WHERE employerid IN (
> 	SELECT id FROM employers
> 	WHERE country='USA'
> )
> 
> I can achieve the desired result in the following way:
> - first create the subquery
> - retrieve the result list
> - use the result list in the IN criteria for the outer query.
> 
> The number of elements in the IN clause is limited in Oracle however, so I
> want to use a subquery instead of an explicit list.
> 
> Does it seem like a good enhancement to have a subquery construct for the
> IN-clause?
> 
> Remke Rutgers
> Technisch Consultant Knowledge & Learning
> 
> Bright Alley Knowledge & Learning
> Media Park - Sumatralaan 45
> Postbus 10 - 1200 JB Hilversum
> Web www.brightalley.com
> Telefoon direct (035) 6775668
> Fax (035) 6774355
> E-mail remke.rutgers@brightalley.nl
>  
> 
> --
> To unsubscribe, e-mail:
<mailto:turbine-torque-dev-unsubscribe@jakarta.apache.org>
> For additional commands, e-mail:
<mailto:turbine-torque-dev-help@jakarta.apache.org>



--
To unsubscribe, e-mail:
<mailto:turbine-torque-dev-unsubscribe@jakarta.apache.org>
For additional commands, e-mail:
<mailto:turbine-torque-dev-help@jakarta.apache.org>

Mime
View raw message