CODEWITHSUNDEEP
javasqldb2
J Max
J Max

Reputation: 2381

Select query with multiple where clauses

I have a list of serial numbers: 111111, 222222, AAAAAA, FFFFFF and I want to return a corresponding value or null from a table depending on whether or not the value exists.

Currently I loop through my list of serial numbers, query using the following statement:

"SELECT cnum FROM table WHERE serial_num = " + serialNumber[i];

and then use the value if one is returned.

I would prefer to do this is one query and get results similar to:

 Row | cnum
 ------------
   1 |  157
   2 |  4F2
   3 |  null
   4 |  93O
   5 |  null
   6 |  9F3

Is there a query to do this or am I stuck with a loop?

Upvotes: 0

Views: 1161

Answers (5)

WarrenT
WarrenT

Reputation: 4532

It sounds as if you have some sort of Java Array or Collection of serial numbers, and perhaps you want to check to see if these numbers are found in the DB2 table, and you'd like to do the whole list all at once, rather than one at a time. Good thinking.

So you want to have a set of rows with which you can do a left join to the table, with null indicating that the corresponding serial was not in the table. Several answers have started to use this approach. But they are not returning your row number, and they are using SELECT UNION's which seems a round-about way to get what you want.

VALUES clause

Your FROM clause can be a "nested-table-expression" which can be a (fullselect) with a correlation-clause. The (fullselect) can, in turn, be a VALUES clause. So you could have something like this:

FROM (VALUES (1, '157'), (2, '4F2'), (3, '5MISSING'), (4, '93O'), ...
     ) as Lst (rw, sn)

You can then LEFT JOIN this to the table, and get a two-column result table like you asked for:

SELECT Lst.rn, t.serial_num
  FROM (VALUES (1, '157'), (2, '4F2'), (3, '5MISSING'), (4, '93O'), ...
       ) as Lst (rw, sn)
  LEFT JOIN sometable t   ON t.serial_num = Lst.sn

With this method, you will probably need a loop to build your dynamic SQL statement string, using the values from your collection.

If it was embedded SQL, we might be able to reference a host array variable containing your serial numbers. But alas, in Java I am not sure how to manage using the list directly in SQL, without using some loop.

Upvotes: 1

İsmet Alkan
İsmet Alkan

Reputation: 5447

I'm not sure if I get you correctly, but this could help:

 String query = "SELECT cnum FROM table WHERE ";
 for(int i = 0; i < serialNumber.length; i++)
      query += "serial_num='" + serialNumber[i] + "' OR ";
 query += "serial_num IS NULL "
 System.out.println(query);

Upvotes: 0

joefromct
joefromct

Reputation: 1556

If you use only an "in" it is not going to return null for the missing value forcing you to do some coding in the application (probably the most efficient way).

If you wanted the database to do all the work (may or may not be ideal) then you would have to trick db2 into returning your list regardless.

Something like this might work, faking the null values to be returned from sysdummy with the common table expression (with part): 

with all_serials as (
select '111111' as serialNumber from sysibm.sysdummy1 union all ,
select '222222' as serialNumber from sysibm.sysdummy1 union all ,
select 'AAAAAA' as serialNumber from sysibm.sysdummy1 union all ,
select 'FFFFFF' as serialNumber from sysibm.sysdummy1 
) 
select 
    t1.serialNumber, 
    t2.serialNumber as serialNumberExists
from 
    all_serials  as t1 left outer join 
    /* Make sure the grain of the_Table is at "serialNumber" */ 
    the_table    as t2 on t1.serialNumber = t2.serialNumber

Upvotes: 1

user359040
user359040

Reputation:

Try something like:

select t.cnum 
from 
(select '111111' serial_num from sysibm.sysdummy1 union all
 select '222222' serial_num from sysibm.sysdummy1 union all
 select 'AAAAAA' serial_num from sysibm.sysdummy1 union all
 select 'FFFFFF' serial_num from sysibm.sysdummy1) v
left join table t on v.serial_num = t.serial_num

Upvotes: 0

Pete
Pete

Reputation: 6723

You can use the SQL IN keyword. You'd need to dynamically generate the list, but basically it'd look like:

SELECT cnum FROM table WHERE serial_num in ('111111', '2222222', '3333333', 'AAAAAAA'...)

Upvotes: 0

Related Questions