Writing a SQL query over a given set of values

Question

I am trying to write a plsql query that allows me to query a set of known values that are not stored in a table. Say those known values are the following strings:

abc
def
ghi
jkl

I would like to achieve something like the following:


select * from [fill-in-the-blank] myvalues 
where not myvalues in 
(
    select id from dbtable
)

..where I am trying to ascertain which of those know values are not in a database table.

Constraints

This is pl/sql (oracle)
This solution must run from within Oracle PL/SQL Developer
I only have read access to the schema so I cannot create temporary tables.

Any ideas?

John N · Accepted Answer · 2011-09-23T11:04:57.200

5

You could use a Common Table Expression (CTE) to accomplish this:

with cte as (
    select 'abc' as id from dual
    union all
    select 'def' from dual
    union all
    select 'ghi' from dual
    union all
    select 'jkl' from dual
)
select * 
from cte
where not id in 
(
    select id from dbtable
)

In fact, you may not even really need the CTE at all (though I find it aids readability):

select * 
from (
    select 'abc' as id from dual
    union all
    select 'def' from dual
    union all
    select 'ghi' from dual
    union all
    select 'jkl' from dual
)
where not id in 
(
    select id from dbtable
)

edited Sep 23 '11 at 11:04

answered Sep 22 '11 at 14:27

John N

1,755
17
21

What is that `myvalues` ? When I run the first example I get `ORA-00904: "MYVALUES": invalid identifier`. – user272735 Sep 22 '11 at 14:40
That's what happens when you copy and paste from the question without properly correcting it. ;-) Corrected - cheers! – John N Sep 22 '11 at 15:25

score 0 · Answer 2 · answered Aug 01 '16 at 15:00

Old thread I know, but nobody mentioned

select * from table(sys.dbms_debug_vc2coll('abc','def','ghi','jkl'));

You don't have to use sys.dbms_debug_vc2coll, of course. Available collection types can be listed using:

select c.owner, c.type_name, c.elem_type_name, c.length
from   all_coll_types c
where  c.coll_type = 'TABLE'
and    c.elem_type_name = 'VARCHAR2'
order by 1,2;

Writing a SQL query over a given set of values

2 Answers2