Q:

Get the root node from leaf in Oracle on the basis of current date

I want to identify the root node on the base of leaf and the current date.
I have a table tree_table with data
  child_node    parent_node    start_date     end_date
           A              B    1/1/2009       31/12/2050
           B              C    1/1/2009       31/12/2010
           C              D    1/1/2009       31/12/2050
           D              E    1/1/2009       31/12/2050

to fetch the root node i am using the Query
select parent_node from(
SELECT parent_node
FROM tree_table exp
where connect_by_isleaf = 1 and sysdate between start_date and end_Date
START WITH child_node = 'A'
CONNECT BY PRIOR parent_node = child_node order by rownum desc)  where rownum=1

But the problem here is that as you can see the second record doesn't satisfy the condition. Stil I am getting the output
Output: E

Which is incorrect i was supposed to get the Output B
Can you please help me out. What i am doing wrong in my query.
I am using Oracle database.

A:

I've not tested this but I believe you have an order of operation issue.  The where clause occurs after the hierarchy is traversed; when I believe you want the date condition to be part of the hierarchy traverse.
I think you want the date check to occur as part of the connect by so it stops processing the hierarchy once the date condition is encountered.
  SELECT paren_node 
  FROM (SELECT parent_dode
        FROM tree_table exp
        WHERE connect_by_isleaf = 1 
        START WITH child_node = 'A'
        CONNECT BY PRIOR pared_node = child_node 
                     AND sysdate between start_date and end_Date
        ORDER BY rownum desc)  
   WHERE rownum = 1

