Search code examples
sqloracle-databaserecursive-queryansi-sqlconnect-by

How to make pl/sql code to Ansi-Sql?

My question may be not challenging for sql expert. i want to rewrite my sql as a ansi-sql. How can i change below sql to ansi-sql in Oracle?

select * 
from TEST r
start with r.childid=@CHILDID 
connect by prior r.PARENTID=r.childid and r.recordstatus=1
Solution

  • The ANSI SQL equivalent would be a recursive common table expression:

    with recursive tree as (
   select * 
   from test
   where childid = .... --<< this is the START WITH part
   union all
   select child.* 
   from test child
     join tree parent ON child.parentid = parent.childid and child.recordstatus = 1  --<< this is the CONNECT BY part
) 
select *
from tree

    I'm not 100% if you also want to apply the recordstatus = 1 condition to the recursion start.

    Oracle doesn't comply with the standard here, and you are not allowed to use the recursive keyword.

    So you need to remove recursive from the query above (the same is true for SQL Server)

    More details about recursive common table expressions (called "subquery factoring" in Oracle) can be found in the manual:

    https://docs.oracle.com/database/121/SQLRF/statements_10002.htm#SQLRF55268