The forum will be down for scheduled maintenance on Saturday, March 4 beginning at 10am EST. Actual downtime is unknown but may be several hours.

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 ;-(

asked 02 Jun '15, 11:11

Stalker's gravatar image

Stalker
23081019
accept rate: 0%


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

permanent link

answered 02 Jun '15, 11:24

Volker%20Barth's gravatar image

Volker Barth
29.5k291441646
accept rate: 32%

edited 03 Jun '15, 06:42

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:

×405
×83
×20

question asked: 02 Jun '15, 11:11

question was seen: 492 times

last updated: 03 Jun '15, 06:42