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.

asked 04 Sep '15, 02:49

LoganTennis's gravatar image

LoganTennis
1216712
accept rate: 100%

1

@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;
(08 Sep '15, 07:54) Breck Carter

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%20Barth's gravatar image

Volker Barth
29.5k291441646
accept rate: 32%

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

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.

permanent link

answered 08 Sep '15, 06:29

Volker%20Barth's gravatar image

Volker Barth
29.5k291441646
accept rate: 32%

edited 09 Sep '15, 03:35

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:

×101
×47

question asked: 04 Sep '15, 02:49

question was seen: 1,893 times

last updated: 09 Sep '15, 03:35