SAP SQL Anywhere

Question

I'm using the following query type quite a lot (joining a stored procedure to a table):

SELECT L.rPartijCnt FROM vkreg, LATERAL( GRN_PartijCreate(vkreg.siteid,vkreg.id,1) with (rSucces tinyint,rPartijCnt integer,rColliPerDc integer,rColliRest integer) ) L

But whenever I add an extra column to the result set of the procedure (GRN_PartijCreate) I have to change the queries using this procedure and add the new column to this "with" clause too (although I'm not intrested in the values)

Is there a way to use this procedure and only descripe the columns I actually want to use somehow (using sqlanywher 11 and up)?

thanks in advance

Accepted Answer

0

The docs state that

Specifying LATERAL (table-expression) is equivalent to specifying LATERAL (SELECT * FROM table-expression).

and your procedure call is a table-expression so you might simply use something like

... LATERAL(SELECT rPartijCnt FROM GRN_PartijCreate(vkreg.siteid,vkreg.id,1) ...

to solve that problem. You might even be able to omit the WITH clause then, but note, I have not tested that.

permanent link

answered 01 Oct '16, 07:56

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

edited 01 Oct '16, 07:58

Hi Volker,

good to point me to the docs!

That led met to the "CROSS APPLY" section, and when using "cross apply" I apparently can omit the parameter specification and that's a really cool feature!

(03 Oct '16, 06:04) Franky

Replies hidden

Yes, I usually prefer CROSS APPLY, too, although it's not ANSI SQL...

(03 Oct '16, 06:22) Volker Barth

flat view

SAP SQL Anywhere

not fully describing result set of stored procedure in queries

Follow this question

Related questions