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

Question

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

`@CHILDID` is not a valid PL/SQL variable reference. Where and how are you running this statement? — , Nov 09 '15 at 08:09
Do you want ANSI SQL as defined by ISO/ANSI, or are you migrating to a specific dbms, e.g. MS SQL Server? — jarlh, Nov 09 '15 at 08:53

score 1 · Accepted Answer · 2015-11-09T08:59:33.773

1

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

edited Nov 09 '15 at 08:59

answered Nov 09 '15 at 08:08

Note: if the OP is moving to SQL Server (as possibly hinted by the `@` for parameters), then SQL Server doesn't support this syntax, but does support this idea, it merely requires the `recursive` keyword to be left out. – Nov 09 '15 at 08:13
@hvd: Penguen specifically asked for ANSI SQL and the `recursive` keyword is required according to the standard. – Nov 09 '15 at 08:16
I am aware of that, and I am not trying to say your answer is wrong for the question that the OP actually asked. However, I suspect the OP asked the wrong question. – Nov 09 '15 at 08:18
it makes "missing keyword" error. drawing "tree" below. i guess something wrong in "with recursive tree" – Penguen Nov 09 '15 at 08:39
"C:\Program Files (x86)\PLSQL Developer\plsqldev.exe – Penguen Nov 09 '15 at 08:44
"pl sql developer" i am using. Yes Oracle database. – Penguen Nov 09 '15 at 08:46
@Penguen: well Oracle doesn't support the ANSI SQL syntax. You need to remove the `recursive` keyword. – Nov 09 '15 at 08:59

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

1 Answers1