|
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 |
|
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; 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 '17, 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 '17, 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 '17, 09:15)
Bjarne Anker
Replies hidden
So feel free to mark the answer as "accepted", said the Forum Nanny:)
(04 May '17, 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. |
|
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. 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 '17, 05:22)
Bjarne Anker
|
