Returning result even for elements in IN list that don't exist in table

Question

I am trying to find the easiest way to return a result set that indicates if some values are or are not present in a table. Consider this table:

id 
------
  1
  2
  3
  7
  23

I'm going to receive a list of IDs and I need to respond with the same list, indicating which are present in the table. If the list I get looks like this: '1','2','3','4','8','23', I need to produce a result set that looks like this:

id  |  status
-------------
  1 | present
  2 | present
  3 | present
  4 | missing
  8 | missing
 23 | present

So far, I've managed to come up with something using UNPIVOT:

select id, 'present' as status
from my_table
where id in ('1','2','3')
union
select subq.v as id, 'missing' as status
from (
        select v
        from
        (
          (
            select '1' v1, '2' v2, '3' v3 from dual
          )
          unpivot
          (
            v
            for x in (v1,v2,v3)
          )
        )
      ) subq
where subq.v not in
(
   select id
   from my_table 
   where id in ('1','2','3')
);

It looks a little weird, but it does work. The problem with this is the select '1' v1, '2' v2, '3' v3 from dual part: I have no idea how I can populate this with a JDBC prepared statement. The list of IDs is not fixed, so each call to the function that uses this query could pass a different list of IDs.

Are there any other ways to get this done? I think I'm missing something obvious, but I'm not sure...

(working with Oracle 11)

Answer 1

From the SQL side you could define a table type and use that to join to your real data, something like:

create type my_array_type as table of number
/

create or replace function f42 (in_array my_array_type)
return sys_refcursor as
  rc sys_refcursor;
begin
  open rc for
    select a.column_value as id,
      case when t.id is null then 'missing'
        else 'present' end as status
    from table(in_array) a
    left join t42 t on t.id = a.column_value
    order by id;

  return rc;
end f42;
/

SQL Fiddle demo with a wrapper function so you can query it directly, which gives:

        ID STATUS             
---------- --------------------
         1 present              
         2 present              
         3 present              
         4 missing              
         8 missing              
        23 present              

From Java you can define an ARRAY based on the table type, populate from a Java array, and call the function directly; your single parameter bind variable is the ARRAY, and you get back a result set you can iterate over as normal.

As an outline of the Java side:

int[] ids = { 1, 2, 3, 4, 8, 23 };
ArrayDescriptor aDesc = ArrayDescriptor.createDescriptor("MY_ARRAY_TYPE",
  conn);
oracle.sql.ARRAY ora_ids = new oracle.sql.ARRAY(aDesc, conn, ids);

cStmt = (OracleCallableStatement) conn.prepareCall("{ call ? := f42(?) }");
cStmt.registerOutParameter(1, OracleTypes.CURSOR);
cStmt.setArray(2, ora_ids);
cStmt.execute();
rSet = (OracleResultSet) cStmt.getCursor(1);

while (rSet.next())
{
    System.out.println("id " + rSet.getInt(1) + ": " + rSet.getString(2));
}

Which gives:

id 1: present
id 2: present
id 3: present
id 4: missing
id 8: missing
id 23: present

As Maheswaran Ravisankar mentions, this allows any number of elements to be passed; you don't need to know how many elements there are at compile time (or deal with a theoretical maximum), you aren't limited by the maximum number of expressions allowed in an IN or by the length of a single delimited string, and you don't have to compose and decompose a string to pass multiple values.

---

As ThinkJet pointed out, if you don't want to create your own table type you can use a predefined collection, demonstrated here; the main function is the same apart from the declaration of the parameter:

create or replace function f42 (in_array sys.odcinumberlist)
return sys_refcursor as
...    

The wrapper function populates the array slightly differently, but on the Java side you only need to change this line:

ArrayDescriptor aDesc =
  ArrayDescriptor.createDescriptor("SYS.ODCINUMBERLIST", conn );

Using this also means (as ThinkJet also pointed out!) that you can run your original stand-alone query without defining a function:

select a.column_value as id,
case when t.id is null then 'missing'
else 'present' end as status
from table(sys.odcinumberlist(1, 2, 3, 4, 8, 23)) a
left join t42 t on t.id = a.column_value
order by id;

(SQL Fiddle).

And that means you can call the query directly from Java:

int[] ids = { 1, 2, 3, 4, 8, 23 };
ArrayDescriptor aDesc = ArrayDescriptor.createDescriptor("SYS.ODCINUMBERLIST", conn );
oracle.sql.ARRAY ora_ids = new oracle.sql.ARRAY(aDesc, conn, ids);

sql = "select a.column_value as id, "
    + "case when t.id is null then 'missing' "
    + "else 'present' end as status "
    + "from table(?) a "
    + "left join t42 t on t.id = a.column_value "
    + "order by id";
pStmt = (OraclePreparedStatement) conn.prepareStatement(sql);
pStmt.setArray(1, ora_ids);
rSet = (OracleResultSet) pStmt.executeQuery();

while (rSet.next())
{
    System.out.println("id " + rSet.getInt(1) + ": " + rSet.getString(2));
}

... which you might prefer.

There's a pre-defined ODCIVARCHAR2LIST type too, if you're actually passing strings - your original code seems to be working with strings even though they contain numbers, so not sure which you really need.

Because these types are defined as VARRAY(32767) you are limited to 32k values, while defining your own table removes that restriction; but obviously that only matters if you're passing a lot of values.

Answer 2

SQL Fiddle

Oracle 11g R2 Schema Setup:

create table IDs (id) AS
            SELECT 1 FROM DUAL
  UNION ALL SELECT 2 FROM DUAL
  UNION ALL SELECT 3 FROM DUAL
  UNION ALL SELECT 7 FROM DUAL
  UNION ALL SELECT 23 FROM DUAL
/

Query 1:

Input the IDs as a string containing a list of numbers and then use a hierarchical query and regular expressions to split the string into rows:

WITH input AS (
 SELECT '1,2,3,4,8,23' AS input FROM DUAL
),
split_inputs AS (
 SELECT TO_NUMBER( REGEXP_SUBSTR( input, '\d+', 1, LEVEL ) ) AS id
 FROM input
 CONNECT BY LEVEL <= REGEXP_COUNT( input, '\d+' )
)
SELECT s.id,
 CASE WHEN i.id IS NULL THEN 'Missing' ELSE 'Present' END AS status
FROM split_inputs s
 LEFT OUTER JOIN
 IDs i
 ON ( s.id = i.id )
ORDER BY
 s.id

Results:

| ID |  STATUS |
|----|---------|
|  1 | Present |
|  2 | Present |
|  3 | Present |
|  4 | Missing |
|  8 | Missing |
| 23 | Present |