How to "default" a column in a SELECT query

Question

Say I have a database table T with 4 fields, A, B, C, and D. A, B, and C are the primary key. For any combination of [A, B], there is always a row where C == spaces. There may or may not be other rows where C != spaces. I have a query that gets all rows where [A, B] == [in_a, in_b], and also where C == in_c if such a row exists, or C == spaces if the in_c row doesn't exist. So, if there is a row that matches the particular C value, I want that one, otherwise I want the spaces one. It is very important that if there is a matching C row, that I not be returned the spaces one along with it.

I have a working query, but its not very fast. This is executing on DB2 for z/OS. I have full control over these tables, so I can define new indicies if needed. The only index on the table right now is [A, B, C], the primary key. This SQL is kinda messy, and I feel theres a better way to accomplish this task. What can I do to make this query faster?

The query I have now is:

SELECT A, B, C, D FROM T
WHERE A = :IN_A AND B > :IN_B AND
       (C = :IN_C 
        OR (NOT EXISTS(
            SELECT B FROM T WHERE 
              A = :IN_A AND B > :IN_B AND C = :IN_C)) 
            AND C = " ");

Answer 1

Caveat emptor, as I am not familiar with DB2 SQL...

You could try using an ORDER BY clause to sort the matching rows such that a row with c = spaces is last in the sorted set, then retrieve just the first row of the set. Something like:

select first
    A, B, C, D
  from T
  where A = :IN_A
    and B = :IN_B
  order by C desc;

This assumes that the FIRST and ORDER BY DESC clauses do what I expect them to.

Answer 2

This will work on DB2 LUW, not sure if the order by clause works on DB2 Z:

select 
   a, b, c, d 
  from t 
 where a = :IN_A 
   and b = :IN_B 
   and c in (:IN_C,'  ') 
order by 
   case c when '  ' then 2 else 1 end 
fetch first 1 row only

Make sure that the ' ' value matches the actual value of the column.

Good luck,

Answer 3

Why not start up the index advisor and reads its advices? (or is this only on DB2 for i/OS?)

We use the advisor for our very big production environment and it gives great advices. But having that said, it's always good to start with a good statement.