The number of elements allowed for an IN statement is governed by Oracle. I run into this problem here. I hit different DBs and they are managed differently. So, I run into this problem from time to time. 1 DBA allows me a 1024 entries and another decides to only allow for 256. You can get around this by dynamically generating your SQL and daisy chaining IN statements with an OR clause between them. Good times.
-Ian >>> "Vorce, Tim (T.)" <[EMAIL PROTECTED]> 11/08/02 07:46AM >>> I'm a fan of the in statement. It's easy to construct the statement with a join (I always need quotes around my values), and my guess is that it is more efficient. There is a limit in my configuration (I think its the oracle that kills it, but it may be perl), that at about 1,000 entries, it breaks. Tim Vorce Ford Motor Company [EMAIL PROTECTED] -----Original Message----- From: Chuck Tomasi [mailto:ctomasi@;plexus.com] Sent: Friday, November 08, 2002 9:40 AM To: [EMAIL PROTECTED] Subject: What's more Efficient - or/in Currently I have a Perl app that reads a config file and creates an SQL "where" clause. For legacy reasons, the clause comes out like this: where group=1001 or group=1005 or group=1017 or group=1025 I was doing some reading the other day and noticed this could be done like this: where group in (1001,1005,1017,1025) Obviously it is a shorter statement, but is it any better? Currently my app support MySQL 3.23.x and Oracle 8.1.7 which both support the "in" semantic. I could change my app, but I'd like to know if spending a few hours to reconstruct the SQL statement is worth it. --Chuck
