The forum will be down for maintenance over the weekend of August 18-20, 2017. The forum will be shut down on the evening (EDT) of Friday, August 18. Downtime is unknown but may be up to two days. The forum will be restarted as soon as maintenance is complete.

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

asked 18 Apr '12, 15:23

Murray%20Sobol's gravatar image

Murray Sobol
46225
accept rate: 0%

edited 18 Apr '12, 15:46

Volker%20Barth's gravatar image

Volker Barth
30.9k309457667


I'm not really familiar with Oracle so I can just give two hints:

  • For the hierarchical query: SQL Anywhere uses RECURSIVE UNIONs (from the SQL Standard) to build recursive queries, cf. this current FAQ and Breck Carter's blog article.
    The particular pseudo columns like LEVEL must (and can) be simulated in the recursive query, cf. the "level" column in Breck's sample.

  • SQL Anywhere does not support stored functions that return a cursor; instead it has stored procedures that can return result sets - and those can be used inside the FROM clause of a query block in case you have to join the result with other tables.

permanent link

answered 18 Apr '12, 15:38

Volker%20Barth's gravatar image

Volker Barth
30.9k309457667
accept rate: 33%

edited 18 Apr '12, 15:45

Regarding the return of a cursor, the last query executed will be returned automatically without defining a cursor. Example:

CREATE OR REPLACE PROCEDURE get_name_address_list  
   (IN p_name_and_address_id      varchar)
BEGIN  
   SELECT c.name_and_address_id,           
          c.full_name,           
          c.parent_record_id,           
          LEVEL pass    
     FROM s1_name_and_address c
END
permanent link

answered 19 Apr '12, 06:17

MichaelMangelschots's gravatar image

MichaelMange...
120119
accept rate: 0%

edited 19 Apr '12, 06:18

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;
permanent link

answered 19 Apr '12, 06:43

Leonid%20Gvirtz's gravatar image

Leonid Gvirtz
2814615
accept rate: 0%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×27
×17
×13

question asked: 18 Apr '12, 15:23

question was seen: 1,536 times

last updated: 19 Apr '12, 06:43