I am having a very strange issue in SQL Anywhere version 11.0.1.3158, here is a very simple sample (in real life there are much more columns and conditions):

CREATE TABLE t1 (c1 integer, c2 integer);
INSERT INTO t1 VALUES (123, 456);

CREATE PROCEDURE proc1 (in @a1 integer)
BEGIN
    SELECT c2 FROM t1 WHERE c1 = @a1;
END;

CREATE PROCEDURE proc2 (in @a1 integer)
BEGIN
    SELECT c2 FROM proc1(@a1);
END;

SELECT (select c2 from proc2(123)) works as intended,

SELECT (select c2 from proc2(c1)) FROM t1 also works,

but SELECT 123 as a, (select c2 from proc2(a)) gives an error Column 'a' not found

and SELECT c1 as a, (select c2 from proc2(a)) FROM t1 also gives me same error,

Why can't I pass an alias to proc2 as an argument in SA11 or SA9? SA16 works without issues.

SELECT 123 as a, (select c2 from proc1(a)) also works (not in SA9 though), but I need proc2().

asked 15 Dec '16, 03:11

Valdas's gravatar image

Valdas
28791222
accept rate: 80%

edited 15 Dec '16, 03:12


When you try to call a stored procedure with a column as argument and want to join the procedure's result set with other columns, you are basically making a LATERAL call, and therefore I would recommend to try that or the alternative CROSS APPLY operator, i.e. something like

SELECT a, SP.c2
FROM (SELECT 123 as a) DT, LATERAL (proc2(DT.a)) SP;

or

SELECT a, SP.c2
FROM (SELECT 123 AS a) DT CROSS APPLY proc2(DT.a) SP;

Note: I've checked that with v12.0.1.4436, which also accepts your sample query "SELECT 123 as a, (select c2 from proc2(a))" but rejects "SELECT c1 as a, (select c2 from proc2(a))", so I'm not sure whether both suggested queries work with v11...

permanent link

answered 15 Dec '16, 03:26

Volker%20Barth's gravatar image

Volker Barth
29.5k291441646
accept rate: 32%

edited 15 Dec '16, 03:32

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:

×137

question asked: 15 Dec '16, 03:11

question was seen: 115 times

last updated: 15 Dec '16, 03:32