Re: Overriding derived values

"David Cressey" <david.cressey_at_earthlink.net> wrote in message news:D%ijf.7219$N45.6710_at_newsread1.news.atl.earthlink.net...
>
> "Ira Gladnick" <ijgla_spamoff_at_clas.ucsb.edu> wrote in message
> news:1133301822.531682.206070_at_z14g2000cwz.googlegroups.com...
> > > ...three possible values:
> > > 0 - Default. Grant beer discount only if average is above 200
> > > 2 - Force. Grant discount regardless of the bowler's average.
> > > 3 - Deny. Deny discount regardless of the bowler's average.
> >
> > Q. What happened to "1"?
> > A. Dyslexia. (Originally numbered options as 1-2-3, then decided
> > first option should be 0 but neglected to renumber the others.)
> >
> > Sample values were just by way of example, however--I didn't intend
> > that this column necessarily had to be numeric.
> >
>
> At the risk of hijacking the thread, there is another alternative....
>
> Three possible states for the variable:
>
> 0 or N -- Deny, Deny discount in accordance with manual request
> 1 or Y -- Force, Grant discount in accordance with manual request.
> NULL -- no manual request, apply the usual rule, in this case being if
> the average is over 200.
>
> The advantage of this method is that it takes advantage of the SQL NULL
> feature to indicate a third alternative. In this case
> it's that the manual policy maker has said nothing pertaining to this
case.
>
> Here's where this coms in handy.... Let's say you work for a few years
> without the manual override capability. You now want to add data and
logic
> for a possible manual override.
>
> You do this:
>
> ALTER TABLE BOWLERS ADD COLUMN MANUAL_DISCOUNT BOOLEAN;
>
> (BOOLEAN is a user defined domain, with values 'Y', 'N'). The existing
> rows will all get filled in with NULLS, which is the
> right answer....
>
> UPDATE BOWLERS
> SET DISCOUNT = 'Y' WHERE AVERAGE > 200;
> UPDATE BOWLERS
> SET DISCOUNT = 'Y' WHERE AVERAGE > 200;
> UPDATE BOWLERS
> SET DISCOUNT = MANUAL_DISCOUNT WHERE MANUAL DISCOUNT IS NOT NULL;
>
> You get the answer you're loooking for.
>
> This can obviously be done with only one sweep through the BOWLERS table,
> but I wanted to keep the example simple.
>
> It's easy to extend this concept to a case where the derived value has
more
> than two possibilites: all you have to do
> is make sure the derived value and the manual override are drawn from the
> same domain. The non-value NULL
> is used to indicate that no manual override has been asked for.
>
>
Oops!

Post in haste, repent at leisure....

The above should have been:

UPDATE BOWLERS
    SET DISCOUNT = 'Y' WHERE AVERAGE > 200; UPDATE BOWLERS
    SET DISCOUNT = 'N' WHERE AVERAGE <= 200; UPDATE BOWLERS
   SET DISCOUNT = MANUAL_DISCOUNT WHERE MANUAL DISCOUNT IS NOT NULL; Received on Wed Nov 30 2005 - 16:32:04 CET