Re: Resolutions and/or Overrides

Kenneth Downs wrote:
> I call the operation an "override" based on my view of how it works, but it
> was called a "resolution" in the shop where I saw the idea most fully
> developed.
>
> The basic idea is this. The table of ITEMS (PK: ITEM) lists item WIDGET
> with a price of $100.00. Except that another table, TAB2, cross-references
> CUSTOMER_TYPE and ITEMS, and lists WIDGET as costing $95.00 for customer
> type "Dealer" and $90.00 for customer type "Distributor". It has no entry
> for the third customer type, "Retail." The rub is that the combination of
> customer type and item may or may not be in the cross-reference. If it is
> there, you use it, otherwise you don't.
>
> Another simple example might be a purchasing system in which you have agreed
> upon prices for some items from some vendors, but otherwise you get on the
> phone and just go with the lower price and type it in manually. The price
> chosen is a function of two columns, the item and vendor, and there may or
> may not be an entry in the item-vendor table.
>
> It gets more complicated, but the rest is just an embellishment of this
> basic idea.
>
> The problem becomes, how do I know what price to put on an order? The shop
> where I saw this used so pervasively called this a "resolution" because
> they would do it procedurally, looking first in the second table and
> failing that falling back to the ITEMS table. Hence they were resolving
> the price.
>
> Upon seeing this, and the performance troubles it gave, I tried to recast
> the problem and saw it as an "override", where I could have a view of the
> two tables wherein anything in table2 would override what was in table1.
> By pulling prices out of this view I could do it in only one pull, with the
> override logic buried in the view definition.
>
> The only thing that really bugs me is that this is the only thing that shop
> did that surprised me, as by this point I thought I had seen everything.
> For some reason I never needed to do this myself, and I don't see a lot of
> people talk about it. Yet in their world, it always seemed to be the Right
> Thing, they had this all over the place.
>
> Any stories on how you have handled these cases would be much appreciated.

Hmmm - isn't that done with an outer join plus COALESCE or NVL? I'm not sufficiently clear on how it would be done in ISO SQL, so you get to suffer from Informix's dialect instead.

Also, in future, please include sufficient details for the schema to list all the columns that might be relevant - and none that aren't - to the question. I've had to assume that Items.Item can contain WIDGET (as a string), which seems implausible, and guess that the column Price exists in both Items and Tab2, and so on. It's really difficult since TAB2 might be referencing a third table CUSTOMER_TYPE about which we know nothing - and we don't know how the customer type is determined and so on. In other words, this is a typical specification - but it is sufficiently badly specified that a consultant can keep busy for a long time challenging the terms and clarifying what is meant. That's not fair in a news group.

SELECT COALESCE(Tab2.Price, Items.Price)

     FROM Items, OUTER Tab2
     WHERE Items.Item = Tab2.Item
       AND Tab2.Customer_Type = "Distributor"
       AND Items.Item = "WIDGET";

Basically, if there's a value in Tab2 for the price, it will be used; if not, Tab2.Price will be null and COALESCE will return the value in Items.Price instead.

This might be translated to the following (faulty) ISO SQL:

SELECT COALESCE(T_Price, I_Price)

     FROM Items LEFT OUTER JOIN Tab2 ON Item
     WHERE Customer_Type = "Distributor"
       AND Items.Item = "WIDGET";

I'm not sure about the column naming - not at all sure. But that gives the gist of it.

-- 
Jonathan Leffler                   #include <disclaimer.h>
Email: jleffler_at_earthlink.net, jleffler_at_us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/