Converting an Oracle Function

[Former Member]

I need to convert the followinf function to SQL Anywhere. Here is the code:

CREATE OR REPLACE FUNCTION f_get_name_address_list  
   (p_name_and_address_id      IN  varchar2)
RETURN SYS_REFCURSOR 
IS  
    v_ret                      SYS_REFCURSOR;
BEGIN  
OPEN v_ret FOR    
   SELECT c.name_and_address_id,           
          c.full_name,           
          c.parent_record_id,           
          LEVEL pass    
     FROM s1_name_and_address c    
    START WITH c.name_and_address_id = p_name_and_address_id    
  CONNECT BY NOCYCLE PRIOR c.name_and_address_id = c.parent_record_id;  
RETURN v_ret;
END;

How do I handle "Start with" and "Connect by"?

I am using SQL Anywhere 11.0.1.2755

[Former Member]

See the example below, I hope it will help. Note that Oracle also supports recursive subquery factoring starting from version 11R2. As far as I know, there is no direct equivalent of NOCYCLE clause in SQL Anywhere. The number of recursions is limited by max_recursive_iterations option. Contrary to Oracle, you don't need a refcursor to return a result set from a stored procedure, it occurs automatically. If you return a result set from a procedure then you can query it, i.e. put it into the FROM clause in a query, much like a regular table.

create table s1_name_and_address
(
    name_and_address_id int,
    full_name varchar(50),
    parent_record_id int null
);

insert into s1_name_and_address values (1, 'test_01', null);
insert into s1_name_and_address values (2, 'test_02', null);
insert into s1_name_and_address values (3, 'test_03', 2);
insert into s1_name_and_address values (4, 'test_04', 2);
insert into s1_name_and_address values (5, 'test_05', 3);
insert into s1_name_and_address values (6, 'test_06', 2);
insert into s1_name_and_address values (7, 'test_07', 1);
insert into s1_name_and_address values (8, 'test_08', null);

commit;

select * from s1_name_and_address

with recursive name_and_address
(
    name_and_address_id,
    full_name,
    nest_level
)
as
(
    select 
        name_and_address_id,
        full_name,
        0 nest_level
    from s1_name_and_address
    where parent_record_id is null
    union all
    select
        a.name_and_address_id,
        a.full_name,
        b.nest_level + 1
    from 
        s1_name_and_address a
        inner join name_and_address b
            on  a.parent_record_id = b.name_and_address_id
            and a.parent_record_id <> a.name_and_address_id
            and b.nest_level <= 20
)
select 
    name_and_address_id,
    full_name,
    nest_level
from name_and_address
order by name_and_address_id;