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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.