SQL & PL/SQL

You cannot have more than 1000 literals in an IN clause. You can, however, have SELECT statements in your IN clause which can return an unlimited number of elements i.e.

SELECT shogen_no,
       shogen_desc,
       disp_turn
  FROM shogen
 WHERE shogen_no IN ( SELECT shogen_no 
                        FROM <<some other table>> )

Justin
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC

You might try using 'between' clause replacing 'in'... check documentation for correct syntax on using between.

Just add some ORs . . .

where
shogen_no in ('one', 'two', 'three') or
shogen_no in ('four', 'five', 'six') or ...

adding 'OR' makes the query extremely slow..... :(

Presumably, if you can gather the literals in a PL/SQL stored procedure, you can gather them in a single SQL statement and just embed that in your IN clause though. Not only will it be faster to execute, you don't have to worry about doing all that string manipulation.

Justin
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC

I just responded to this same issue. Don't use the IN clause if at all possible when it is driven by a subquery. First you can return too many rows for IN clause itself (which is what you are seeing). Second, the performance of IN in this context is poor. Oracle doesn't handle the relationship well.

Select
shogen_no,
shogen_desc,
disp_turn
From
shogen
where
shogen_no in ('one', 'two', 'three', ..........'more than thousand')

Change to

select ...
from shogen
where
exists (select 1 from table used to build shogenList where shogenlist.value = shogen.value)

This example would be better if you had included the original query to build shogenlist. We have rewritten a ton of our queries using this syntax. Check out the explain when you are done.

IN and EXISTS both have their uses. If shogen is a multi-million row table, IN will probably be faster. If shogen has a few hundred rows, EXISTS will probably be faster. It it is in the middle, it's a good idea to try both.

Additionally, EXISTS doesn't work particularly well when you have hard coded values.

Justin
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC

Hi,

this error means

ORA-01795 maximum number of expressions in a list is 1000
Cause: More than 254 columns or expressions were specified in a list.
Action: Remove some of the expressions from the list.

One way you can use a OR conditions like
shogen_no in (...)
or shogen_no in ().....


thanks

Firstly, well done for answering a thread that's 3.5 years old and secondly well done for answering it with an answer that's already been given and thirdly well done for answering it with an answer that does not offer the best solution to the problem as has already been explained.

Good to see you read the threads you are answering.

I stumbled upon this thread when I was searching for a solution to this issue. I was exploring if there are any alternatives to just replacing the string with a IN with a select. I hit on this solution, which was very interesting, it uses an array of table. Take a look:

http://www.orafaq.com/forum/t/41437/0/

PS: I know I am posting to an older thread, but only to keep the information in this thread in case it is useful to others. :)

Here are some failed solutions and a solution that works…

This problem is caused by the fact that in Oracle a comma-delimited list of expressions can contain no more than 1,000 expressions. Microsoft SQL Server Reporting Services (SSRS) lets you run a query that populates a check-box drop-down, and when the user hits “Select All” the values get passed as a single-quoted comma-separated list of values to the parameter used in the SQL of the report, triggering the Oracle error when it exceeds 1,000.

Changing SQL does not seem to be a solution. Defining a function that takes a string returning a TABLE TYPE or ARRAY, which then goes to a “in ( Select * from TABLE(CAST(” works for small sets but not when the literal string has more than 4,000 characters. Even literals in the form of Q’{…}’ (to hide the imbedded single quotes) can’t have more than 4,000 characters. The SQL-only method below converts the parameter to a table function fails when the literal string has more than 4,000 characters, which is typically the case when you have more than 1,000 items. For future reference here is that approach if you ever need it:

--execute one-time definitions outside of SSRS
CREATE OR REPLACE TYPE StringTable AS TABLE OF VARCHAR2( 250 );
/
CREATE OR REPLACE FUNCTION f_string_table ( p_list IN VARCHAR2 ) RETURN StringTable
IS
v_delimiter CHAR(3) := ''',''';
v_string LONG := SUBSTR( p_list || ',''', 2 );
v_pos pls_integer;
v_data StringTable := StringTable( );
BEGIN
LOOP
v_pos := instr( v_string, v_delimiter );
EXIT WHEN( NVL( v_pos, 0 ) = 0 );
v_data.extend;
v_data( v_data.count ) := trim( SUBSTR( v_string, 1, v_pos - 1 ) );
v_string := SUBSTR( v_string, v_pos + 3 );
END LOOP;
RETURN( v_data );
END f_string_table;
/
--test
SELECT * FROM TABLE( CAST( f_string_table( Q'{'aaaa','bbbb','c','xxxx'}' ) AS StringTable ) );

--in the SSRS SQL change this
--IN(:myparm)
--to this
--IN(select * from table(cast(f_string_table(Q'{:myparm}') as StringTable));

Converting the parm string to a multi-row Table Value Constructor does not work either, due to the same 4,000 character limitation, and due to the fact that Oracle, unlike SQL Server, does not allow multi-row Table Value Constructors in the form of:
SELECT * FROM (VALUES (101, 'Bikes'),(102, 'Accessories'),(103, 'Clothes')) AS Category(CategoryID, CategoryName);

So it appears you need to change both the user interface in SSRS and the Oracle SQL, by either, (1) adding check-boxes for when a user wants to select all the values in a domain, or (2) alter the query that populates the drop-downs such that it adds a “ SELECT ALL” choice to the values that the final query uses to control predicates.

Here is how to do (2): Add a new item at the head of the list with the literal value “ SELECT ALL” and make “ SELECT ALL” the single default value for the parameter, instead of checking ‘Select All Values’ as the defined default setting.

My assumption is that no user would actually want to take the time to manually select a subset with more than 1,000 items for a set with more than 1,000 items unless they wanted to select all items. The exception to this, which is not solved below, is the situation where a user wants to select all 1,000+ items except a few items.

In this solution the drop-down will show both “Select All” and “ SELECT ALL” but with “ SELECT ALL” checked by default. If the user ignores this and clicks on “Select All” (which actually causes all values to be passed to the query) no harm or error will be done unless the number of items exceeds 1,000, which will teach the user to put it back to “ SELECT ALL”. Users with a smaller domain of parameter values will not be impacted with either selection.

A secondary impact of this approach below may be that queries run a little faster with “ SELECT ALL”.

Find the parameters that exceed 1,000 items. For these parameters do the following:

Insert the following line above the ORDER BY clause that provides the drop-down values to the user:
UNION ALL SELECT ' SELECT ALL' FROM DUAL
The initial space in the first character of ' SELECT ALL' is for the purpose of causing it to sort to the top and to prevent it from matching a value found in the data.
(You may need to add a column with a -1 if your parameter returns an identifier and use -1 to signify the select all case.)

In the report’s query, wherever you see the parameter, such as:
AND M.MATERIALDESC IN (:MyParm)
Change it to:
AND (' SELECT ALL' IN( : MyParm) OR M.MATERIALDESC IN( : MyParm))
Note: keep the OR’d predicates in the order shown as Oracle processes OR left-to-right and eliminates checking the second predicate after the first TRUE. This helps performance.

In the SSRS parameter definition make “ SELECT ALL” the single default value for the parameter, instead of checking ‘Select All Values’ as the defined default setting.

Not pretty but it works.

Hi;
You can try doing this

Select
shogen_no,
shogen_desc,
disp_turn
From
shogen
where
shogen_no in ('one', 'two', 'three', ..........'one thousand')

UNION ALL

Select
shogen_no,
shogen_desc,
disp_turn
From
shogen
where
shogen_no in ('onethousandone', 'onethousandtwo', 'onethousandthree', ..........'two thousand')


Regards..