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

asked 01 Oct '16, 07:01

Franky's gravatar image

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

Volker Barth
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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



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:


question asked: 01 Oct '16, 07:01

question was seen: 1,749 times

last updated: 03 Oct '16, 06:22