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

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

4john — Sat, 09 Jul 2011 15:50:01 GMT

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.

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

SASJedi — Sun, 10 Jul 2011 19:08:25 GMT

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

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

4john — Mon, 11 Jul 2011 15:03:20 GMT

Thanks SASjedi.It really helped.