DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 16
Accepted Solution

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

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.


Accepted Solutions
Solution
‎07-10-2011 03:08 PM
SAS Employee
Posts: 104

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

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

View solution in original post


All Replies
Solution
‎07-10-2011 03:08 PM
SAS Employee
Posts: 104

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

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

Occasional Contributor
Posts: 16

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

Thanks SASjedi.It really helped.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 1861 views
  • 1 like
  • 2 in conversation