0

I have a C/C++ program with embedded Oracle SQL calls in proc (11.2+ version). I have a list of values (int) as an array. I would like to check through a SQL query (as proc code) which values are not in a DB table. For example, say, I have values: 10, 20, 30, 40, 50 stored in an array and my DB table t1 has column col1 that has 10, 20, 40. So,

Select col1 from t1

will provide me:

10
20
40

So, I am looking for the excluded values, i.e. 30, 50. Can I do this through an embedded Pro*C SQL query? My list is quite large and the DB table has many values.

Dr. Debasish Jana
  • 6,980
  • 4
  • 30
  • 69
  • 1
    Where's the list of values coming from? If they were in the database it'd be trivial to return your desired result. – Hart CO Aug 11 '15 at 17:29
  • What database are you using? Please tag the question appropriately. – Gordon Linoff Aug 11 '15 at 17:31
  • I'm using Oracle, and I will write the code as embedded SQL in Pro*C for Oracle, I have an array of numbers in my C/C++ code, that I would like to check non-present in an Oracle DB table – Dr. Debasish Jana Aug 11 '15 at 18:04
  • possible duplicate of [find the missing values from a set of values, using SQL](http://stackoverflow.com/questions/11464543/find-the-missing-values-from-a-set-of-values-using-sql) – Bulat Aug 11 '15 at 23:41

3 Answers3

4

You need to set up your values in a table. Here is a typical way what will work in most databases:

with list as (
      select 10 as n union all select 20 union all select 30 union all
      select 40 union all select 50
     )
select l.*
from list l
where not exists (select 1 from t1 where col1 = l.n);

The syntax might vary depending on the database (from dual, a subquery instead of a CTE), but the idea is the same.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • This is cool. But, my list is quite large, I have the list of values as a C/++ array and I am trying to write an embedded SQL in Pro*C for Oracle. – Dr. Debasish Jana Aug 12 '15 at 02:32
2

In Oracle you could use a collection as an alternative to creating a CTE/inline view using unioned queries:

select l.column_value as missing
from table(sys.odcinumberlist(10, 20, 30, 40, 50)) l
where not exists (select 1 from t1 where col1 = l.column_value);

   MISSING
----------
        20
        30
        50

SQL Fiddle.

Here sys.odcinumberlist is a built-in collection type, to save you creating your own table type.

Aside from being slightly easier to type the values in like that (IMO), you can also use this approach from a client application by passing the values as an array, which can be useful. There are examples here for OCI (C/C++) or here for Java.

Community
  • 1
  • 1
Alex Poole
  • 183,384
  • 11
  • 179
  • 318
0
SELECT regexp_substr('10|20|30|40|50', '[^|]+', 1, ROWNUM) var
FROM dual
CONNECT BY LEVEL <= length(regexp_replace('10|20|30|40|50', '[^|]', '')) + 1
MINUS
SELECT col1 FROM t1;
Bulat
  • 6,869
  • 1
  • 29
  • 52
Abid Jaffri
  • 1