Usenet.com

www.Usenet.com

Group Index

Comp Thread Archive from Usenet.com

<-- __Chronological__ --> <-- __Thread__ -->

UNION to implement default values



before i go flapping my gums and get deeper in alligators, wondering
whether the following makes sense.

i have a database which provides picklists for input.  it started out with a 
2 column key in the cross-reference table to the values table, call
them company and state.  it's a transitive closure.  along the way,
naturally, more columns have been added, call them region and division.
so that now, the unique identifier from the cross-reference to the
values would be:  company + division + region + state.  the transitive
closure just got a whole lot bigger.

my colleagues are complaining.  they want to have a "default" row, which
is read, then make an attempt to read on the full key values, which if
it fails, use the previously read default.  i don't like this, since the
backend accepts the data blindly, on the assumption that it has been
pre-edited.  the pick-list values are modifiable in real-time, so i object
to doing multiple selects to derive these pre-edited values.

they claim, which may be true, that, while possible, there is rarely in
fact any difference among the pick-lists for a given column based upon
the values in the cross-reference key.  so, they don't like having to 
maintain the TC.

i'm not even sure that the syntax is permited, which (psuedo-codely) i
think is this:

select * from CrossReference as cr  where cr.blah = default
join Values vl on cr.vlId = vl.id
UNION
select * from CrossReference as cr2  where cr2.blah = specific
join Values vl2 on cr2.vlId = vl2.id

in most cases, the second select is expected to fail.  i can't find any
explanation in my reference material whether one can still get a result
set, nor whether join-union-join is permited.

this seems a reasonable way to implement pick-lists.  has anyone done it
this way?

thanks,
robert



<-- __Chronological__ --> <-- __Thread__ -->


Usenet.com



Please check out one of the premium Usenet Newsgroup Service Providers below for access to Usenet.