Sybase SA12: Possible error in the SQL-query when join to a stored procedure

[former_SQLA_member1694881]

Sybase SQL Anywhere 12.0.1.4134

This script to reproduce the problem:

CREATE TABLE "DBA"."TEST1" (
    "ID" INTEGER NOT NULL,
    "CTEXT" CHAR(100) NULL,
    PRIMARY KEY ( "ID" ASC )

) go

CREATE TABLE "DBA"."TEST2" (
        "ID" INTEGER NOT NULL,
        "MASTER_ID" INTEGER NULL,
        "CSTR2" CHAR(10) NULL,
        PRIMARY KEY ( "ID" ASC )
)
go

CREATE PROCEDURE "DBA"."MyProc"(in nMasterCode integer)
BEGIN
 select ID, MASTER_ID, CSTR2 from dba.TEST2 where MASTER_ID = nMasterCode;
END
go

INSERT INTO "DBA"."TEST1" ("ID","CTEXT") VALUES(1,'str1');
INSERT INTO "DBA"."TEST2" ("ID","MASTER_ID","CSTR2") VALUES(1,1,'dstr1');

Now, perform basic query:

select * from dba.TEST1
join dba.MyProc(TEST1.ID) on (MyProc.MASTER_ID = TEST1.ID)

i get an error message:

Could not execute statement. Illegal reference to correlation name 'TEST1' SQLCODE=-824, ODBC 3 State="42S02"

though if the request to change bit

select * from dba.TEST1
join dba.MyProc(1) on (MyProc.MASTER_ID = TEST1.ID)

it is executed without error.

That is the Problem that the SA12 is why it does not like the transfer of the table field as a parameter to the SP ( MyProc(TEST1.ID) ).

Q: This is my fault, or this is a bug SA12 ?

P.S. Sorry this error I can not check on the more recent versions (builds) SA12, so I do not have a subscription to technical support and version SA12 Developers stuck on 12.0.1.4134 ;-(

[VolkerBarth · Accepted solution]

AFAIK, you will need a LATERAL procedure call (or the OUTER/CROSS APPLY join operator) when you want to call a procedure with arguments taken from a table...

---

I'd like to add a link to a great (as usual) Glenn Paulley article comparing LATERAL and the APPLY operators:

From the Archives: Cross and Outer Apply