UNION to implement default values

[robert]

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

[Knut Stolze]

robert <gn*****@rcn.com> wrote:

before i go flapping my gums and get deeper in alligators, wondering
whether the following makes sense. [...] 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
You might want to have a look at the syntax for a "subselect" and the "from
clause" in particular. The JOIN itself is syntactically incorrect.

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

in most cases, the second select is expected to fail.

You mean that the second select does not return any row, right? (Fail
implies an error to me, no-row is a success condition.) A union between
something and the empty set is possible and just returns "something" again.

What you have to keep in mind, however, is that set as in SQL are not
ordered. So there is per-definition no "first" or "second" row. If you
want to have a specific order like the rows from the first subselect should
come first, then you will need an ORDER BY clause like this:

select *
from ( select 1, *
from CrossReference as cr JOIN Values v12 ON cr.vlId = vl.id
where cr.blah = default
UNION
select 2, *
from CrossReference as cr2 join Values vl2 on cr2.vlId = vl2.id
where cr2.blah = specific ) AS t(ord, ...)
order by ord

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[robert]

Knut Stolze <st****@de.ibm.com> wrote in message news:<bq**********@fsuj29.rz.uni-jena.de>...

robert <gn*****@rcn.com> wrote:

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

[...]

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

You might want to have a look at the syntax for a "subselect" and the "from
clause" in particular. The JOIN itself is syntactically incorrect.

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

in most cases, the second select is expected to fail.

You mean that the second select does not return any row, right? (Fail
implies an error to me, no-row is a success condition.) A union between
something and the empty set is possible and just returns "something" again.

What you have to keep in mind, however, is that set as in SQL are not
ordered. So there is per-definition no "first" or "second" row. If you
want to have a specific order like the rows from the first subselect should
come first, then you will need an ORDER BY clause like this:

select *
from ( select 1, *
from CrossReference as cr JOIN Values v12 ON cr.vlId = vl.id
where cr.blah = default
UNION
select 2, *
from CrossReference as cr2 join Values vl2 on cr2.vlId = vl2.id
where cr2.blah = specific ) AS t(ord, ...)
order by ord

dang. i always get the where clause out of place. compiler does remind me,
though. and by error, SQL-100 was the issue. that isn't an issue. i'm
still puzzled why this technique isn't in any of my "standard" texts,
which made/makes me nervous that there is some syntactic or logical
hidey-hole into which i'll fall. it would seem to be a fairly common
application.

if Mr. Celko is lurking: would this be a useful solution to the TC
sections? <G>

thanks,

robert

[--CELKO--]

>> I have a database which provides picklists for input ... the unique
identifier from the cross-reference to the values would be: company +
division + region + state. <<

Have you looked at using a nested sets model for the hierarchy?

[robert]

jo*******@northface.edu (--CELKO--) wrote in message news:<a2**************************@posting.google. com>...

I have a database which provides picklists for input ... the unique

identifier from the cross-reference to the values would be: company +
division + region + state. <<

Have you looked at using a nested sets model for the hierarchy?

yes...but my colleagues haven't been able to digest adjaceny list, and
would rather just write lots of separate queries. life is still seen
through the lens of the static copybook; occurs and redefines literally
implemented in the datastore (it's an insult to say DataBase).
it's a struggle.