BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
4john
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
SASJedi
SAS Super FREQ

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

Check out my Jedi SAS Tricks for SAS Users

View solution in original post

2 REPLIES 2
SASJedi
SAS Super FREQ

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

Check out my Jedi SAS Tricks for SAS Users
4john
Calcite | Level 5

Thanks SASjedi.It really helped.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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