CODEWITHSUNDEEP
sqloracle-databaselist
Tony
Tony

Reputation: 2406

SQL-Statement to use predefined values list as an SQL-table

I have a list of ids. e.g. (1, 2, 10). Is it possible to use that list as a table in Oracle ?

I mean something like that:

select * from (1, 2, 10) as x, some_table
where x not in some_table.value

Upvotes: 4

Views: 3660

Answers (3)

Yaroslav Shabalin
Yaroslav Shabalin

Reputation: 1644

select to_number(regexp_substr(str, '[^,]+', 1, level)) ids
--put your comma-delimited list in the string below
  from (select '0,12,2,3,4,54,6,7,8,97' as str from dual)
connect by level <= length(regexp_replace(str, '[^,]+')) + 1
minus
--here comes select from some_table
select level from dual connect by level <= 10;

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You can do this with a subquery:

select st.*
from (select 1 as x from dual union all
      select 2 as x from dual union all
      select 10 as x from dual
     ) t left outer join
     some_table st left outer join
     on st.value = t.x
where st.value is null;

Upvotes: 2

Dmitrii Bychenko
Dmitrii Bychenko

Reputation: 186668

You can use with construction

with x as (
  select 1 as v from dual
  union all
  select 2 from dual
  union all
  select 10 from dual
)

select *
  from x, some_table
 where x.v not in some_table.value -- <- or whatever condition(s) required

Upvotes: 3

Related Questions