Re: Nested Coalescing possible in SQL?

From: Lennart Jonsson <lennart_at_kommunicera.umea.se>
Date: 3 Jun 2004 20:39:11 -0700
Message-ID: <6dae7e65.0406031939.1ed5fa49_at_posting.google.com>

lennart_at_kommunicera.umea.se (Lennart Jonsson) wrote in message news:<6dae7e65.0406022337.338870_at_posting.google.com>...
> jlanfield2003_at_yahoo.com (Jeff Lanfield) wrote in message news:<235c483f.0406021038.13a1b83b_at_posting.google.com>...

[...]

Hmmm, I must have been just a little bit tired there ;-) Should read (diff in snd part of union):

 with suspects (nodeid, suspectid, depth) as (

     select
         a.nodeid, a.ancestorid,
         (select count(1) from ancestor where nodeid = a.ancestorid)
as depth
     from ancestor a, data d
     where a.ancestorid = d.nodeid
       and d.color is not null
     union all
     select
         t.nodeid, t.nodeid,
         (select count(1) from ancestor where nodeid = t.nodeid) as
depth
     from data d, tree t
     where d.nodeid = t.nodeid
       and d.color is not null

 )
 select s.nodeid, d.color from suspects s, data d  where d.nodeid = s.suspectid
   and depth = (select max(depth) from suspects where nodeid = s.nodeid)
;

NODEID COLOR
----------- ----------

          5 GREEN     
          7 GREEN     
          8 GREEN     
          9 BLUE      

 

/Lennart Received on Fri Jun 04 2004 - 05:39:11 CEST