02-07-2013 06:48 PM
I have used nested query to query on a variable that is already indexed, but SAS returns the following message:
INFO: Index BaseCardNumber not used. Sorting into index order may help.
The following is the code:
create table CCF_GDN as
where BaseCardNumber not in (select distinct ACCT_NBR
and EffectiveDate between '02oct2012'd and '02jan2013'd
So I'm wondering how should I write this in another way to optimise the search by using the index on the variable BaseCardNumber.
Thanks in advance guys.
02-07-2013 07:43 PM
create table ccf_gdn as
left outer join falt2 f2
on f.basecardnumber = f2.acct_nbr
f.effecdtivedate between '02oct2012'd and '02jan2013'd
and f2.acct_nbr is null;
02-07-2013 11:11 PM
Thanks on the reply, I guess it is a possible solution, because I tried and the joining does use the index. However I'm interested in querying BaseCardNumber NOT in acct_nbrs, so can't really do that with joins right.....?
And the answer I was really after is how to still do the nested query, but using the index to make it faster, because the error message says order of the observation is not the same as in the index, how can I fix that problem?
Thanks in advance.
02-08-2013 12:04 AM
How would it use an index for NOT in query? I can see if you have a list of IDs you can look them up in the index. But how could you use the index to look up a the ones that aren't in your list?
02-08-2013 08:25 AM
that's the purpose of doing a left join and then requiring the value to be null. It actually accomplishes the same things as a "not in"...
from t1 left outer join t2 on t1.col1 = t2.col1
where t2.col1 is null
is functionally equivalent to
select * from t1 where col1 not in (select col1 from t2);
I'm a bit surprised that the not exists doesn't use the index on t2.
select * from t1 where not exists (select * from t2 where col1=t1.col1)
I would have thought that it did.
select * from