SAP SQL Anywhere

Question

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.

Accepted Answer

2

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

permanent link

answered 04 Sep '15, 03:26

Volker Barth
40.2k●362●550●822
accept rate: 34%

1

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

(04 Sep '15, 07:01) Christian Ha...

Replies hidden

Good catch!

(04 Sep '15, 07:27) Volker Barth

flat view

Answer 2

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.

SAP SQL Anywhere

How to left join to a stored procedure

Follow this question

Related questions