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

          Thanks
          • 17. Re: question about dates
            6363
            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
              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
                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