Post reply

CTE in Oracle

  • October 24, 2005 at 11:39 am

    #95464

    Hi,

    I have some data in a source system that is a PERFECT fit for a recursive CTE but unfortunately the data is in oracle.

     

    Does anyone know if Oracle has some similar abilities to a CTE?

     

    Thanks in advance

    -Jamie

    P.S. I believe I could set this up as a linked server and execute a CTE against it (is that correct???) but I'd rather not do that!

     

    Jamie Thomson
    http://sqlblog.com/blogs/jamie_thomson

  • October 24, 2005 at 1:08 pm

    #599643

    If this is a hierarchy, see oracle's proprietary "connect by".

    SQL = Scarcely Qualifies as a Language

  • October 24, 2005 at 2:16 pm

    #599661

    Sorry, I should have been clearer in my original post. It is indeed a hierarchy in a self-referencing table.

    If CONNECT BY will do the job then that's great news. Thanks Carl!

     

    -Jamie

     

    Jamie Thomson
    http://sqlblog.com/blogs/jamie_thomson

  • October 25, 2005 at 7:58 am

    #599799

    Hello Jamie,

    With Oracle you have a lot of flexibility in doing hierarchical queries.

    Not only CONNECT BY but also many other analytic functions:

    RANK, DENSE_RANK, LEAD, LAG, ... to use with OVER (PARTITION BY ...)

    Look in the Oracle doc.

    Regards,

    Carl

  • October 25, 2005 at 8:22 am

    #599815

    Thanks Carl. I'm using CONNECT BY and its working a treat. I'm quite impressed actually. Same functionality as a CTE yet alot less wordy.

    And before anyone castigates me...yes I know CTEs do alot more besides

    -Jamie

     

    Jamie Thomson
    http://sqlblog.com/blogs/jamie_thomson

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply