Can't Exceed 1000 Expressions in Query

[grimlockpdp]

I am trying to run the following query below in Oracle:

Select Acct_Num, Customer_Name
From Database
Where Acct_Num IN (
1,
2,
..,
..,
200001,
200002
)

When I attempt to run the query, I get the error message: "ORA-01795 maximum number of expressions in a list is 1000"


Is there any way I can execute this query w/o having to break the list up by 1,000 Acct_Num over 200 times? Please note that there are more than 200,002 accounts and I just want to extract information on these 200,002 accounts. Thank you!!

 

[Rockn]

If you want all of the account numbers why are you using a where clause? If you know the range of number use a between statement or something like that.

 

[grimlockpdp]

My example was not very clear. What I meant to say is that the accounts that I wish to retrieve are not all ordered sequentially and mix ranges with accounts that I don't want. Therefore I can't use the between clause.

 

[Rockn]

Is there another filed you can set as a criteria like account type or create your own field based off of another? I kind of figured that was why you were doing it that way.

 

[grimlockpdp]

Unfortunately I can't use another field(s) to isolate the accounts I want to query. I wish I could though.

 

[Rockn]

Could you use an external table set up just for the account numbers you need to report on and loop through that set of data? I am sure the 10,000 limit is a limitation of the SQL language and there is no way around that. Just have to think outside of the box to get it to work the way you want.

 

[grimlockpdp]

Actually I used a subquery list and that worked. Thanks for the help though.

 

[Rockn]

No problem. More than one way to skin a cat.