how to get values not present in the inlist

[zulu99]

hi

i have a table t (col number);

with say 5 values
1
2
3
4
5

when i query select * from t where col in (1,2,3);
it will
output
1
2
3


what i wnat is something like this
when i query select * from t where col in (1,2,3,6,7);

it should give me
6
7

values which are not part of the table


Rgrds
Sushant

[zulu99]

slight error in writing above

what i want is a query which can give the values which i
have passed in the list but are not part of the table

[Jr]

Originally posted by zulu99

what i wnat is something like this
when i query select * from t where col in (1,2,3,6,7);

it should give me
6
7

values which are not part of the table

When you say "Select * from table" then how do u expect values from a list ?
Convert that list to a lookup table and then do a outerjoin query.
Jr

[jmodic]

Not that it's pretty nor is it terribly efficient, but it should work with positive integers (well, at least if numbers in the inlist are not too high - it should work with numbers of at least up to about 20.000) in your inlist:

select col from
(select rownum as col from all_objects
 minus
 select col from t
)
where col not in (1,2,3,6,7);