Maximum number of elements ina an IN() Clause

[BAC]

XP Pro, Office 2007 SP1
What is the maximum number of elements that can be included in an IN() Clause:

e.g.
Select *
From Units
Where unit number in (1,2,3,4,5,6,7.....)

I"ve found limits for "AND" operators in a WHERE/Having Clause (and I'm
guessing that holds for "OR"s as well) , and I'm hinking that would limit me
to 100 IN() elements.

TIA

 

[Dale Fye]

I'm not sure I've ever seen an actual limit to the number of items you can
use in an IN ( ) clause. However, this method is inherently slow, and the
more elements you add, the slower it gets.

Don't know where you are getting the values to include inside the
parenthesis, but what you might consider is some sort of temporary table
where you can insert these values and then use an INNER JOIN in your SELECT
statement between your table and the temp table.

 

[BAC]

Thanks

That was my initial plan, but I'm working with a local Access dBase that is
linked to a remote ODBC dBase.

The remote table I need to link to has more than 255 fields and several of
the fields I need to retrieve are out beyond the 300 mark, meaning I have to
use a Pass-through query to access those fields. Unfortunately, I cannot use
a local table in a join on a pass through.

I have successfully used the IN() clause for smaller lists, but I just got a
project pulling several thousand records. So while waiting on IT to build me
a new view with those "beyond the 300 mark" fields, I was going to work
around the problem by running a query with groups of IN() elements and
appending the results to a local table.

Of course the number of times I would have to run the query would be:
( Number of records needed/max number of elements in the IN() clause + 1)

Any suggestions on another way to get around local table in a Pass-through
query problem?

TIA

 

[Bob Barrows]

Thanks

That was my initial plan, but I'm working with a local Access dBase
that is linked to a remote ODBC dBase.

The remote table I need to link to has more than 255 fields and
several of the fields I need to retrieve are out beyond the 300 mark,
meaning I have to use a Pass-through query to access those fields.

Well, then the Access limitation is irrelevant isn't it? Your query will
be using the limitations of the remote database, whatever that is.. The
only limitation that Access will impose is the number of characters in
the sql statement, which, I believe, is 64000.

Unfortunately, I cannot use a local table in a join on a pass through.

I have successfully used the IN() clause for smaller lists, but I
just got a project pulling several thousand records. So while waiting
on IT to build me a new view with those "beyond the 300 mark" fields,
I was going to work around the problem by running a query with groups
of IN() elements and appending the results to a local table.

Of course the number of times I would have to run the query would be:
( Number of records needed/max number of elements in the IN() clause
+ 1)

Any suggestions on another way to get around local table in a
Pass-through query problem?

Outside of using a passthrough to insert the values into a temp table in
the remote database, I'm out of suggestions.

 

[vanderghast]

I have never done it this way, and unable to test it at this moment, but
would making the passtrough query WITHOUT the in clause and then, a local
query using an inner join between the passtrough query and a local table
is acceptable?

Vanderghast, Access MVP

 

[Bob Barrows]

Wouldn't that be the same as retrieving all the records in the remote
table and filtering them locally?

 

[BAC]

Remote table has 856,000 rows.

Since my only limiting criteria is the unit number of the list in my local
table, it probably could be done, I guess, but might take a while..

thx

 

[MGFoster]

XP Pro, Office 2007 SP1
What is the maximum number of elements that can be included in an IN() Clause:

e.g.
Select *
From Units
Where unit number in (1,2,3,4,5,6,7.....)

I"ve found limits for "AND" operators in a WHERE/Having Clause (and I'm
guessing that holds for "OR"s as well) , and I'm hinking that would limit me
to 100 IN() elements.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

If the unit_number is sequential perhaps you could use the BETWEEN op:

WHERE unit_number BETWEEN 1 and 700

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSpWBP4echKqOuFEgEQI+zwCeMSRaLt/7TXAnz6XLOF9XVazK3TIAnRWP
6gyE/7AF39ZsgGEzBwQnRJKT
=YI8T
-----END PGP SIGNATURE-----

 

[vanderghast]

Indeed, if the IN clause is the ONLY criteria... that hurts.

If you are using MS SQL Server, can you define an heterogenous server, on
it, pointing to an Access-Jet database, and push the values in a table
there? Or even better, create a ##global (or #userOnly) temp table, fill it
with the values for the intersection, and then execute your passthrough
query?


Vanderghast, Access MVP

 

[vanderghast]

If the IN clause is the only criteria, yes. But maybe a criteria using a
BETWEENess MIN_of_in_values AND MAX_of_in_values could help a little...


Vanderghast, Access MVP

 

[BAC]

No on SQL Server, but I'm going to ry your between Min of AND Max of

 

[BAC]

Min of Max of was great idea...

Stilll pulls a lot of records, but mor manageable than 856K+

Thanx much