This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Feb 28, 2013 2:28 PM by user650888 Go to original post RSS
  • 15. Re: question about dates
    user650888 Newbie
    Currently Being Moderated
    Thanks, let me try recursive with clause. I would come back if I fail to get the result
  • 16. Re: question about dates
    user650888 Newbie
    Currently Being Moderated
    Could you please demonstrate 11.2 recursive with clause

    Thanks
  • 17. Re: question about dates
    6363 Guru
    Currently Being Moderated
    http://technology.amis.nl/2009/09/01/oracle-rdbms-11gr2-goodbye-connect-by-or-the-end-of-hierarchical-querying-as-we-know-it/
  • 18. Re: question about dates
    chris227 Guru
    Currently Being Moderated
    user650888 wrote:
    Could you please demonstrate 11.2 recursive with clause

    Thanks
    I dont think that this will do the improvement you are looking for.
    Did you try the model clause?
    Your are probably generating many rows, so you should have enough memory.
    Find out, where your bottleneck is. You shouldnt run into problems with connect by jsut with some hundreds rows.
    And it's not about indexing.
  • 19. Re: question about dates
    user650888 Newbie
    Currently Being Moderated
    This works !
    SELECT  r.myid, 
            r.sd+five_minutes_begin new_begin_date,
            r.sd+five_minutes_end new_ed,
              round(r.value/3,3) value_new
    FROM    mytest r,
            (
             SELECT LEVEL, 
                    ((5*(LEVEL-1))/(60*24)) five_minutes_begin,
                    ((5*LEVEL)/(60*24)) five_minutes_end
             FROM dual
             CONNECT BY LEVEL <= 3
             ORDER  BY 1
            )
    WHERE  trunc((ed-sd)*(24*60*60)) = 900
    UNION ALL
    SELECT  r.myid, 
            r.sd,
            r.ed,
             r.value
    FROM    mytest r
    where     trunc((ed-sd)*(24*60*60)) != 900
    order by myid,new_begin_date
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points