> Thanks for the feedback guys.
> Daniel, the example I posted was as simplistic as I could devise,
> just to show the behavior I was observing. My requirements are far
> more complex, and the query I was trying to construct is given below.
> In a nutshell, the situation is that we have an 'Override' table
> (DATAFIX) that is used to convert or override the values on incoming
> order data. The criteria used to determine if an override should occur
> is to match on 4 distinct column values coming in on the order. For
> simplicity, I've labelled them DATA1 through DATA4. The design of the
> DATAFIX 'override' table is that rows can exist in this table that can
> match on all four column values, or on only one, or any combination.
> The application must find the most 'granular' match and use that row
> to supply override values. That is, if an order has values in all four
> DATA columns, and there is a row on the DATAFIX table that has values
> that match the first two DATA columns and another row that matches on
> all four DATA columns, I want to select the latter row.
> I thought I could implement this 'most granular to least granular'
> prioritizing by using the 'exit on first true condition' nature of the
> CASE statement. So, the WHEN clauses in the CASE statement below are
> ordered in most granular to least granular order.
> Evidently, as Tokunaga T mentioned, I cannot use that method in a
> WHERE clause. If anyone can provide a more elegant solution I would be
> most appreciative.