SAP SQL Anywhere

Question

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().

Accepted Answer

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...

SAP SQL Anywhere

SA11 error passing alias to a nested procedures

Follow this question

Related questions