How to left join to a stored procedure

[Former Member]

In Firebird, I can do the following:

SELECT * FROM TABLE AS A LEFT JOIN SP_CALC( A.COL1 ) AS B ON( 1 = 1 )

This will take the input TABLE.COL1 and return a result list of both table A columns and Stored Procedure B Columns.

Have attempted wothout success to the same in sqlAnywhere.

Using SQL Anywhere 12.

Any Ideas.

[Breck_Carter]

@OtherPeople: The issue for SQL Anywhere is not so much the LEFT OUTER JOIN, it is the use of a column as an argument (which Volker covers in his replies).

According to the Firebird FAQ "How to JOIN a table and selectable stored procedure?" the LEFT JOIN is a requirement when joining a table with a stored procedure call that refers to a column from the table as an argument; i.e., Firebird does not allow for an INNER JOIN... which may in fact be what you want rather than a bunch of NULL values.

Here's the Firebird FAQ, complete with speling misteak and hokey "so they are available" justification :)...

How to JOIN a table and selectable stored procedure?

It's simple, the selectable stored procedure works as any table.

select t1.id, sp1.field1
from t1
join sp1(parameters) on t1.id = sp1.id;

It you wish, you can also use fields from the table as input values into stored procedure. 
It that case, make sure you use LEFT JOIN to force table fields to be evaluated first 
(so they are available to stored procedure):

select t1.id, sp1.field1
from t1
left join sp1(t1.id) on 1=1;

[VolkerBarth · Accepted solution]

You can use a "lateral procedure call" to do so, something like

select * from MyTable as A, lateral (MyProc(A.col1)) as B

See lateral derived tables in the v12.0.1 docs...

[Chris26 · Accepted solution]

Be aware that lateral works like an INNER JOIN and not a LEFT JOIN as was the question.
If you try:

create table t1 ( id integer );

insert into t1 (id) values (1);
insert into t1 (id) values (2);

create procedure Test (IN pId integer)
result (descr varchar(3))
begin
  if pId = 1 then 
    select 'abc';
  endif
end;

select *
  from t1,
       lateral(test(t1.id)) as r

The result is:

id  |  descr
============
1   |  'abc'

So if you want a LEFT JOIN you have to make sure the procedure always has a result set

create procedure Test1 (IN pId integer)
result (descr varchar(3))
begin
  if pId = 1 then 
    select 'abc';
  else
    select null;
  endif
end;

select *
  from t1,
       lateral(test1(t1.id)) as r

then the result is:

id  |  descr
============
1   |  'abc'
2   |  null

[VolkerBarth · Accepted solution]

Good catch!

[VolkerBarth]

Here's a better solution when a left join is required by using the OUTER APPLY operator (which I tend to overlook... - here's a great article, originally from Glenn Paulley😞

create table t1 ( id integer );

insert into t1 (id) values (1);
insert into t1 (id) values (2);

create procedure Test (IN pId integer)
result (descr varchar(3))
begin
  if pId = 1 then 
    select 'abc';
  endif
end;

select * from t1 cross apply test(t1.id);

-- returns
-- 1, 'abc'

select * from t1 outer apply test(t1.id)

-- returns
-- 1, 'abc'
-- 2, null

Apparently, OUTER APPLY does not require that the stored procedure returns a NULL value for missing data.