Convert Oracle Function TO SQL Anywhere 11

[MurraySobol]

I have created an Oracle Function using a reference cursor.
Here is the Oracle version:
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;
/

Can anyone assist me with the translation to SQL Anywhere??
Specificall, I dont know how to handle "Start with" and "connect by".

Thanks for any assistance.

 

[LKBrwnDBA]

Why do you want to translate to SQL Anywhere?
On your previous post you were looking for an Oracle PL/SQL function.

If You need to just compile and test, use an Oracle product like the command line SQL*Plus or the GUI SQL Developer.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[MurraySobol]

I need to translate it to SQL Anywhere because we support 3 database vendors:
Oracle
Microsoft SQL Server
Sybase SQL Anywhere 11

 

[LKBrwnDBA]

It would be easier to translate the M$ SQL server version to Sybase because the T-SQL language are similar, in fact it is said that the M$ T-SQL is based on Sybase T-SQL (I cannot prove this allegation).


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[MurraySobol]

OK, here is the SQL Server version:
CREATE FUNCTION f_get_name_address_list
(@name_address_id varchar(26))
RETURNS @id_table
TABLE (name_and_address_id varchar(26),
full_name varchar(200),
parent_record_id varchar(26),
pass integer
)

AS
BEGIN

/* Usage:
SELECT * FROM f_get_name_address_list('PACMA')
*/

DECLARE @Pass int

INSERT INTO @id_table
SELECT c.name_and_address_id,
c.full_name,
c.parent_record_id,
1
FROM s1_name_and_address c
WHERE c.name_and_address_id = @name_address_id

INSERT INTO @id_table
SELECT c.name_and_address_id,
c.full_name,
c.parent_record_id,
2
FROM s1_name_and_address c
WHERE c.parent_record_id <> c.name_and_address_id
AND c.parent_record_id IN
(SELECT x.name_and_address_id
FROM @id_table x
WHERE x.pass = 1
)

INSERT INTO @id_table
SELECT c.name_and_address_id,
c.full_name,
c.parent_record_id,
3
FROM s1_name_and_address c
WHERE c.parent_record_id IN
(SELECT x.name_and_address_id
FROM @id_table x
WHERE x.parent_record_id <> x.name_and_address_id
AND x.pass = 2
)

INSERT INTO @id_table
SELECT c.name_and_address_id,
c.full_name,
c.parent_record_id,
4
FROM s1_name_and_address c
WHERE c.parent_record_id IN
(SELECT x.name_and_address_id
FROM @id_table x
WHERE x.parent_record_id <> x.name_and_address_id
AND x.pass = 3
)

INSERT INTO @id_table
SELECT c.name_and_address_id,
c.full_name,
c.parent_record_id,
5
FROM s1_name_and_address c
WHERE c.parent_record_id IN
(SELECT x.name_and_address_id
FROM @id_table x
WHERE x.parent_record_id <> x.name_and_address_id
AND x.pass = 4
)

INSERT INTO @id_table
SELECT c.name_and_address_id,
c.full_name,
c.parent_record_id,
6
FROM s1_name_and_address c
WHERE c.parent_record_id IN
(SELECT x.name_and_address_id
FROM @id_table x
WHERE x.parent_record_id <> x.name_and_address_id
AND x.pass = 5
)

INSERT INTO @id_table
SELECT c.name_and_address_id,
c.full_name,
c.parent_record_id,
7
FROM s1_name_and_address c
WHERE c.parent_record_id IN
(SELECT x.name_and_address_id
FROM @id_table x
WHERE x.parent_record_id <> x.name_and_address_id
AND x.pass = 6
)

INSERT INTO @id_table
SELECT c.name_and_address_id,
c.full_name,
c.parent_record_id,
8
FROM s1_name_and_address c
WHERE c.parent_record_id IN
(SELECT x.name_and_address_id
FROM @id_table x
WHERE x.parent_record_id <> x.name_and_address_id
AND x.pass = 7
)

RETURN

END

 

[LKBrwnDBA]

Have you tried executing it on SQL Anywhere?


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[MurraySobol]

Yes, I tried:
Here is the SQL Anywhere version:
CREATE FUNCTION f_get_name_address_list
(name_address_id varchar(26))
RETURNS id_table
TABLE (name_and_address_id varchar(26),
full_name varchar(200),
parent_record_id varchar(26),
pass integer
)

AS
BEGIN

/* Usage:
SELECT * FROM f_get_name_address_list('PACMA')
*/

DECLARE Pass integer

INSERT INTO id_table
SELECT c.name_and_address_id,
c.full_name,
c.parent_record_id,
1
FROM s1_name_and_address c
WHERE c.name_and_address_id = name_address_id

INSERT INTO id_table
SELECT c.name_and_address_id,
c.full_name,
c.parent_record_id,
2
FROM s1_name_and_address c
WHERE c.parent_record_id <> c.name_and_address_id
AND c.parent_record_id IN
(SELECT x.name_and_address_id
FROM id_table x
WHERE x.pass = 1
)

INSERT INTO id_table
SELECT c.name_and_address_id,
c.full_name,
c.parent_record_id,
3
FROM s1_name_and_address c
WHERE c.parent_record_id IN
(SELECT x.name_and_address_id
FROM id_table x
WHERE x.parent_record_id <> x.name_and_address_id
AND x.pass = 2
)

INSERT INTO id_table
SELECT c.name_and_address_id,
c.full_name,
c.parent_record_id,
4
FROM s1_name_and_address c
WHERE c.parent_record_id IN
(SELECT x.name_and_address_id
FROM id_table x
WHERE x.parent_record_id <> x.name_and_address_id
AND x.pass = 3
)

INSERT INTO id_table
SELECT c.name_and_address_id,
c.full_name,
c.parent_record_id,
5
FROM s1_name_and_address c
WHERE c.parent_record_id IN
(SELECT x.name_and_address_id
FROM id_table x
WHERE x.parent_record_id <> x.name_and_address_id
AND x.pass = 4
)

INSERT INTO id_table
SELECT c.name_and_address_id,
c.full_name,
c.parent_record_id,
6
FROM s1_name_and_address c
WHERE c.parent_record_id IN
(SELECT x.name_and_address_id
FROM id_table x
WHERE x.parent_record_id <> x.name_and_address_id
AND x.pass = 5
)

INSERT INTO id_table
SELECT c.name_and_address_id,
c.full_name,
c.parent_record_id,
7
FROM s1_name_and_address c
WHERE c.parent_record_id IN
(SELECT x.name_and_address_id
FROM id_table x
WHERE x.parent_record_id <> x.name_and_address_id
AND x.pass = 6
)

INSERT INTO id_table
SELECT c.name_and_address_id,
c.full_name,
c.parent_record_id,
8
FROM s1_name_and_address c
WHERE c.parent_record_id IN
(SELECT x.name_and_address_id
FROM id_table x
WHERE x.parent_record_id <> x.name_and_address_id
AND x.pass = 7
)

RETURN

END
;
and here is the error:
Could not execute statement
Syntax error near 'id_table' on line 4
SQLCODE=-131,ODBC 3 State=42000

 

[LKBrwnDBA]

Unfortunately I have very little or no experience with T-SQL or SQL Anywhere, perhaps you could post the code in one of the many fine forums corresponding to that language.

At least the Oracle part works...


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb