Oracle – Identifying IN-LIST Values Not Present in Table

oracle

I'm looking for a simple way of seeing which items in my IN-LIST are not actually in the table. It seemed really simple but I think I'm probably over-thinking it.

I have a long list of item codes supplied by a customer and they would like an attribute in the table updating for each of those item codes.
However, I need to let the customer know if any of the codes he has provided don't actually exist in the table.
I've tried doing a COUNT(*) and a GROUP BY and I've tried some variations using MINUS but it always comes down to the fact that if the value isn't in the IN-LIST I can't refer to it.

Example:
Item table has 3 entries

Item_code
12345
98765
65436

User supplies list of

I want to highlight to the customer that the code '81764' is not a valid item code.
I can do this easily with PL/SQL but I would like a SQL solution if possible.

Best Answer

You need to create a list of values which is unfortunately quite cumbersome in Oracle (due to the missing values() clause).

But the following will work:

with id_list as (
  select 12345 as id from dual union all 
  select 98765 from dual union all 
  select 65436 from dual union all 
  select 81764 from dual
)
select il.id as missing_id
from id_list il
  left join item i on i.item_code = il.id
where i.item_code is null;

But given the nature of the query this is a bit complicated to parameterize though. The best solution is probably to create a (global) temporary table, then put the list of IDs you receive into that table and use that instead of the common table expression (the with (...) part)

Edit

There is a (cumbersome) way to "explode" a comma separated list of values, which might be "easier" in this case:

with base_value(id_string) as (
  select '12345,98765,65436,81764' from dual
), id_list(id) as (
  select regexp_substr (id_string, '[^,]+', 1, level)
  from base_value
  connect by regexp_substr (id_string, '[^,]+', 1, level) is not null
)
select il.id as missing_id
from id_list il
  left join item i on i.item_code = to_number(il.id)
where i.item_code is null;

But that might quickly hit Oracle's limit of 4000 bytes for a string literal....

Related Solutions

What’s the best way to list groups of values that are contained in other groups

This is similar to your first approach, Jack, with a few differences.

It does all the counting in batches as opposed to in the HAVING clause. HAVING is a post aggregation filter, which is why sticking the COUNT in there is slow.
It is easier to read by grace of the CTEs which break up the work into smaller chunks.
Like your approach, it works both on Oracle 9.2+ and SQL Server 2005+ (and any other RDBMS that supports CTEs).

Your indexing strategy shouldn't matter that much if you are querying the whole table at once, but if you are interested in specific sets I recommend you have indexes both on (x, y) and (y, x).

One way or the other, this query should run very quickly:

WITH set_sizes AS (
  SELECT x, COUNT(*) AS set_size  -- "size" is a reserved keyword in Oracle
  FROM t
  GROUP BY x
)
, intersection_sizes AS (
  SELECT 
      sub.x     sub_x
    , super.x   super_x
    , COUNT(*)  intersection_size
  FROM 
                t sub
    INNER JOIN  t super
      ON  sub.y = super.y
      AND sub.x <> super.x
  GROUP BY
      sub.x
    , super.x
)
SELECT xs.sub_x, xs.super_x
FROM 
              set_sizes           ss
  INNER JOIN  intersection_sizes  xs
    ON  ss.x = xs.sub_x
    AND ss.set_size = xs.intersection_size
;

Edit: Per your tests against a large data set, it looks like this query is the speediest.

Oracle: How to query a Hierarchical table

FrusteratedWithFormsDesigner has the right direction(+1). Here is what I think you are looking for specifically.

CREATE OR REPLACE VIEW BuildingSubs AS
   SELECT connect_by_root location "Building", location "SubLocation"
   FROM some_table l
   START WITH l.Location IN 
      (
         SELECT location FROM
         (
         SELECT level MyLevel, location FROM some_table 
         START WITH parent IS NULL 
         CONNECT BY PRIOR location=parent
         )
         WHERE MyLevel=3   
      )
   CONNECT BY PRIOR l.location = l.parent;

select * from BuildingSubs; 

Building             SubLocation        
-------------------- --------------------
BLDG-01              BLDG-01              
BLDG-01              Basement             
BLDG-01              Room B-002           
BLDG-01              Floor-01             
BLDG-01              Room 1-001           
BLDG-02              BLDG-02              
BLDG-03              BLDG-03              
Grounds              Grounds

The view accomplishes all three goals. You can query it for a building to find everything it contains and you can query it for a sub-location to find what building it is in.

drop table some_table;
create table some_table (Location Varchar2(20), Parent Varchar2(20));

insert into some_table values ('Org. Name',NULL);
insert into some_table values ('MAINCAMPUS','Org. Name');
insert into some_table values ('BLDG-01','MAINCAMPUS');
insert into some_table values ('BLDG-02','MAINCAMPUS');
insert into some_table values ('BLDG-03','MAINCAMPUS');
insert into some_table values ('Grounds','MAINCAMPUS');
insert into some_table values ('Floor-01','BLDG-01');
insert into some_table values ('Basement','BLDG-01');
insert into some_table values ('Room B-002','Basement');
insert into some_table values ('Room 1-001','Floor-01');

If you don't want to count the building itself as one of the sub-locations you can wrap the existing query in one the eliminates entries in which the building and sublocation are the same.

Best Answer

Related Solutions

What’s the best way to list groups of values that are contained in other groups

Oracle: How to query a Hierarchical table

Related Question