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.
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
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
Thanks SASjedi.It really helped.
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.
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.