Hi.

I've started to look into procedures to handle complex calculations, rather than using PowerBuilder code. In one case, I need more than one return value from my procedure, and I have added those parameters with "OUT" in the list:

Ex.

spw_calc_cost (IN Foo integer, OUT storage numeric(13,3), OUT packing numeric(13,3), OUT freight numeric(13,3))

So the question is, can I use these values in three separate columns in my select?

SELECT ColA, ColB, spw_calc_cost(ColC, ColD, ColE), ColF = ColC + ColD + ColE
FROM TableA
WHERE foo = 'bar';

Wanted result:

ColA    ColB    ColC    ColD    ColE    ColF
Foo     Bar    1.000    2.000   3.000   6.000

Or do I have to write 3 separate functions to achieve this? Or is the solution to create a RESULT return type perhaps?

regards,

Bjarne Maritech Systems AS Norway

asked 04 May, 04:38

Bjarne%20Anker's gravatar image

Bjarne Anker
440161830
accept rate: 0%

edited 04 May, 04:40


Note, the title is not really appropriate, stored procedures may have one single return value (returned via the RETURN statement) but you are using multiple output parameters...

Stored procedures are called via the CALL statement, so the attempt to use them in a SELECT list should fail.

In case your procedure does return a result set (instead of using several output parameters), it can also be used within a FROM clause, such as

CREATE PROCEDURE spw_calc_cost (IN Foo integer)
RESULT (storage numeric(13,3), packing numeric(13,3), freight numeric(13,3))
BEGIN
...
   SELECT ... -- selecting your 3 calculated values
END;

SELECT ColA, ColB, sp.storage, sp.packing, sp.freight, ColF = sp.storage + sp.packing + sp.freight
FROM spw_calc_cost(x) sp;
permanent link

answered 04 May, 06:01

Volker%20Barth's gravatar image

Volker Barth
30.8k308456665
accept rate: 32%

converted 04 May, 07:04

Hi!

Yes, this works very good. Thanks. But what if I need these values in select containing other columns from other tables? Ex. select ItemNo, Description,<storage>,<packing>,<freight> from Items where foo = 'bar';

I suppose I have to use subselects, like this?

select ItemNo, Description, (select storage from spw_calc_cost(x)) as storage, (select packing from spw_calc_cost(x)) as packing, (select freight from spw_calc_cost(x)) as freight from Items where foo = 'bar';

Regards,

Bjarne

(04 May, 06:29) Bjarne Anker
Replies hidden
1

No, you can just add other tables to the FROM clause.

What is the connection between these other tables and the SP? Is the SP based on some column value from other tables (say here, dependent on the ItemNo), or will the SP result set be fully joined with the other tables?

Here's a variant with a CROSS APPLY operator, handy if the SP's input parameter is based on a column value (here I assume the ItemNo):

select ItemNo, Description, storage, packing, freight
from Items cross apply spw_calc_cost(Items.ItemNo) sp
where foo = 'bar';

Note, the according dop topic provides more samples...

(04 May, 07:12) Volker Barth

Exactly what I was looking for!

In short, we have a query today with a couple of columns which is created in the PowerBuilder datawindow with "cast(null as numeric(13,3)) as X". After retrieve of the data, we loop through all the rows in the datawindow to calculate values for all those null-columns. It worked OK before, but now the amount of data has multiplied by the 100's, so is's very slow approach. But when we do the calculation in the database using a procedure, things are speeding up quite a lot.

Thanks! :)

Bjarne

(04 May, 09:15) Bjarne Anker
Replies hidden

So feel free to mark the answer as "accepted", said the Forum Nanny:)

(04 May, 11:42) Volker Barth

To reinforce what Volker said, the SELECT ... FROM procedure-name ( argument-list ) is an incredibly powerful technique for many reasons, not the least of which is the fact you can push an unlimited amount of complexity down into the procedure where it is executed inside the SQL Anywhere engine, thereby taking full advantage of the query optimizer.

In particular, it eliminates Number 21 "Do joins in the application" from the listicle How To Make SQL Anywhere Slow.

permanent link

answered 07 May, 20:15

Breck%20Carter's gravatar image

Breck Carter
26.2k430599862
accept rate: 20%

This isn't an answer to your question, just a tip for programming in PB. Whether the dw is visible or not, until the calculations are completed set redraw off (dw.SetRedraw( False ). Once the processing is finished, turn redraw back on (dw.SetRedraw( True ) ). It's 100's of times faster.

permanent link

answered 05 May, 10:26

Tom%20Mangano's gravatar image

Tom Mangano
632192536
accept rate: 6%

Hi.

We are using setdraw(false) before the processing starts, but the calculation i PowerScript is very slow still. That´s why I want to use a database procedure on retrieve.

Thanks.

Bjarne

(06 May, 05:22) Bjarne Anker
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:

×24
×16
×15

question asked: 04 May, 04:38

question was seen: 170 times

last updated: 07 May, 20:15