ERROR ORA-01795

[inma]

THIS IS THE MESSAGE WHEN I SEARCH MORE THAN 1000 VALUES IN
A COLUMN:
maximum number of expressions in a list is 1000
IS NOT POSSIBLE TO DO THAT?

 

[LKBrwnDBA]

The workaround is to split the condition into separate OR or AND clauses.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

Inma,

Out of curiosity, is the offending "SEARCH" in the form of an "IN-list" in which you have explicitly listed the search values (such as "WHERE ... IN (exp1, exp2, exp3...)) or is it in the form of "WHERE ... IN (SELECT...)"?

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 15:37 (30Nov04) UTC (aka "GMT" and "Zulu"),
@ 08:37 (30Nov04) Mountain Time

 

[inma]

Hi Musafa, I use the first form:
WHERE ... IN (exp1, exp2, exp3...), this is:

set pagesize
set linesize 8
SELECT substr(LTRIM(gridbd,'BR'),1,8) FROM grupmm WHERE gridbd in ('BR00683849'
,'BR00683850'
,'BR00683851'
,'BR10046397'
,'BR00620637'
,'BR00620638'
,'BR00620639'
............................ (more than 1000 values)
,'BR10110737'
,'BR10110738'
,'BR10110739'
) order by gridbd;

 

[SantaMufasa]

Inma,

In that case, may I suggest a MUCH FASTER method to do what you want is:

(one-time only):
create table SEARCH_VALS (vals varchar2(15));
insert into SEARCH_VALS values('BR00683850');
insert into SEARCH_VALS values('BR00683851');
et cetera

(as-needed query):
SELECT substr(LTRIM(gridbd,'BR'),1,8)
FROM  grupmm, search_vals
WHERE gridbd = vals
order by gridbd;

You will be amazed at how much faster this will be. Let us know.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 19:37 (30Nov04) UTC (aka "GMT" and "Zulu"),
@ 12:37 (30Nov04) Mountain Time

 

[inma]

Thanks Musafa,
it's fine to see that help is useful and appreciated.