Hierarchical Query puzzler (why no rows)

Help! I've got a hierarchical query which returns duplicate rows, and I can't use DISTINCT due to interface constraints. The solution proposed was to something like this:

Select * from authorities
 where (delegated_by,delegated_to,category,unit,effdt)  IN
   (Select b.delegated_by,b.delegated_to,b.category,b.unit,b.effdt

      from authorities b
     where b.category  = {valueA}
       and b.unit = {valueB}
       and b.effdt >=
          (Select MAX(c.effdt) from authorities c
            where c.delegated_by = b.delegated_by
              and c.delegated_to = b.delegated_to
              and c.category = b.category
              and c.unit = b.unit
              and c.effdt <= {valueC}
           )
     connect by b.delegated_by = prior b.delegated_to
            and b.category = prior b.category
            and b.unit = prior b.unit
            and b.effdt >= prior b.effdt
     start with b.delegated_to = {valueD}

Now, the sub-query is the guts of this, and it returns 65 rows, 17 of which are distinct. I know this because I ripped it out and ran it on its own. Yet when I run the whole thing, no rows are returned!!!

What's the problem here? What am I doing wrong? My undying gratitude goes to those who can provide an answer.

Larry Tipton
lstipt_at_ccmail.monsanto.com Received on Fri Nov 17 1995 - 00:00:00 CET