IN list operator limit (ERROR : ORACLE prepare error: ORA-00972: identifier is too long)

[4john]

Hi,

Below is a sample code that I have written.I am just wondering if there is any limitation on the number of values IN operator can hold.In the below example SELECT statement in step 5 holds more than 10000 ids.

LOG file shows the ERROR : ORACLE prepare error: ORA-00972: identifier is too long

1     proc sql;

2     create table TEST as

3     select * from

4     oracle.sample

5     where id in ( select unique id from orcl.notice) ;

6     quit;

Any inputs would be really appreciated.

Thanks.

[SASJedi · Accepted solution]

There is a limit to the number of values ORACLE will accept on an IN operator (in ORACLE 10g, 1000 is the max).  Consider using a join to an in-line view.  Something like this might do the trick:

proc sql;
create table TEST as
   select s.* 
      from oracle.sample as s,
          (select unique id from orcl.notice) as list
      where s.id=list.id
;
quit;

Another option is to make a temp table out of the results of the subquery, and use that for a join. 

Otherwise, you might try splitting the list results into segments of 1000 or less and using separate IN operators and logical ORs:
"where ID in (1,2,3...,999) OR ID in(1000,1001,1002,...1999)" etc

[4john]

Thanks SASjedi.It really helped.