Re: UNION to implement default values

Knut Stolze <[EMAIL PROTECTED]> wrote in message news:<[EMAIL PROTECTED]>...
> robert <[EMAIL PROTECTED]> 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