Conditional columns in OR-constrained SQL statements

Question

This question is about a general technique in SQL, that I can't quite work out how to best achieve with my current understanding of the language.

Note that this will be running against Oracle, in case any vendor-specific functionality would be particularly useful, though I expect there is a nice way of doing this with "pure" SQL.

Short version of the question: If I have an OR condition on a query such that I check that a value in my "main" table A corresponds to a value either in table B or in table C, what is the best way to conditionally modify (e.g. multiply) the statements selectors if the value was found in table C but not in table B?

Long version (with example): Currently I have a query that looks roughly like this (it's a lot more complicated, but this is the basic structure):

select o.value, /* other stuff... */
  from orders o,
       clients c
 where o.client = c.pkey
   and c.name = ?

All is fine. However, now imagine that some clients are allowed to act on behalf of others - but because of processing fees or some logic trades carried out this way don't quite have their full value. So there's a table managed_orders with columns for order, client (both primary keys to the respective tables) and another column multiplier, which is a floating point number between 0.0 and 1.0 representing the proportion of the order value that should be "carried through".

Right - so modifying the above query to show orders that are either owned by the given client or managed by them for someone else, is fairly straightforward. However, I would need to multiply the o.value selector by the managed order's multiplier if this is a managed order, but if it's a classic direct order then this isn't needed (or equivalently we could multiply by 1.0).

select o.value * m.multiplier /* Will not work in all cases */, /* other stuff... */
  from orders o,
       clients c,
       managed_orders m
 where (o.client = c.pkey or m.order = o.pkey)
   and c.name = ?
   and m.client = c.pkey

How best to achieve this conditional multiplication?

Answer 1

How about CASE?

SELECT o.value * (CASE 
                     WHEN m.pkey IS NOT NULL THEN m.managed_multiplier /* Might need */
                     ELSE 1.0                                  /* some tweaking here */ 
                 END)
    /* , other stuff... */
FROM orders o
    LEFT JOIN clients c ON (o.client = c.pkey)
        LEFT JOIN managed_orders m ON (m.order = o.pkey)
WHERE 
   c.name = ?

Answer 2

If not all orders have an associated managed_order then you need to use an outer join. You can then deal with the absence of a multiplier like this:

select o.value * COALESCE(m.multiplier,1)
from orders o
left outer join managed_orders m on m.order = o.pkey
left outer join clients c on (o.client = c.pkey or m.client = c.pkey)
where c.name = ?

The COALESCE ensures that if there is no multiplier, the constant 1 is used instead.

Answer 3

Hi dtsazza,

you could use an OUTER JOIN to conditionnaly join two tables. Assuming that all orders have a client and that only the managed orders are present in the MANAGED_ORDERS table, this would apply the modifier only if it is a managed order:

SELECT o.value * nvl(m.multiplier, 1)
  FROM orders o
  JOIN client c ON (o.client = c.pkey)
  LEFT JOIN managed_orders ON o.pkey = m.order
WHERE c.name = ?

Answer 4

could you use UNION to get the required results set.

something like

Select o.value, ... 
FROM Orders o, clients c
WHERE o.client = c.pkey
and c.name = ?
UNION
SELECT o.value * m.multiplier, ...
FROM Orders o, clients c, managed_orders m
WHERE m.order = o.pkey
AND c.name = ?
AND m.client = c.pkey