1

Oracle has a limit on the arguments in the IN statement of 1000, I believe, is there a way around this? If I had a statement similar to this (with 1000 ids or more):

SELECT COUNT(*) 
FROM "VERSION" 
WHERE "VERSION"."VERSION_ID" IN 
      (59156, 57045, 54779, 54048, 54048, 
       --- long list ---
       12648, 11160, 11160, 11160, 11160)

How could I write simple count statement?

  • 1
    Where are these version_id values coming from? 99 times out of 100, they're coming from somewhere else in the database in which case you just need to take the select statement that returns the values and put that in the in clause. If the values are coming from somewhere else, you'd often want to load them into a collection or temporary table and use that in your in clause. Other than that, you can have multiple in clauses or get even more esoteric but that rarely produces code that people are happy to maintain... – Justin Cave Aug 4 '15 at 20:26
  • 1
  • @a1ex07 can you make one of those examples into an answer so I can accept that one. thanks – Gandalf StormCrow Aug 26 '15 at 16:14
2

Try creating a temporary table to hold all of the IDs you want to search on then join in that temporary table.

create table tmpIds(id int not null)
insert into tmpIds (59156)
insert into tmpIds (57045)
etc...

SELECT COUNT(*) 
FROM "VERSION"
INNER JOIN tmpIds ON 
"VERSION"."VERSION_ID" = tmpIds.id

drop table tmpIds
  • 1
    Executing DDL causes implicit commit which can mess the transaction management. A better approach is to use global temporary table instead. – Jussi Aug 5 '15 at 5:58
0

You can also OR IN clauses. Such as:

select count(*)
from version
where version_id in (1,2,3,4,5,...255)
or version_id in(256,257,258,....);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.