| |
Posté le 24 mai 2015 - 14:19 |
WD18
Hi,
Is there a way of calling a global procedure from a Query in order to create a calculated item?
Cheers
Reg |
| |
| |
| | | |
|
| | |
| |
Posté le 24 mai 2015 - 15:26 |
Hi,
You can sent a parameter to the querie. Run the procedure and the result can be passed as a parameter
when you run the querie just do following
queriename.paramater = resultoftheprocedure
hexecutequerie(queriename) |
| |
| |
| | | |
|
| | |
| |
Posté le 24 mai 2015 - 15:29 |
Hi,
I think I see what you mean, but that wouldn't work in this case. My query would be returning many rows, and for each row I want to run a procedure that will calculate a value using a global_procedure. |
| |
| |
| | | |
|
| | |
| |
Posté le 25 mai 2015 - 13:11 |
Ah I get it
Hexecutequerie( myquery)
//then to run a procedure for each row of the result
For each myquery
run procedure
end
Regards
Allard
PS
As you see you can do loops etc on queries as you would do on tables. |
| |
| |
| | | |
|
| | |
| |
Posté le 25 mai 2015 - 13:17 |
No, that's not what I mean I'm afraid. I want to run a procedure from within the query such that the result of each execution of the procedure is contained in a calculated control within the query.
Whereas a calculated item in a query can refer to an internal function, e.g. Round, I want to refer to a function (ie a Global Procedure) that I have written myself, so in the calculated item box it would say something like "= gp_myfunction(parm1)" |
| |
| |
| | | |
|
| | |
| |
Posté le 25 mai 2015 - 14:05 |
SolutionJ-Reg
You cannot do what you want directly however a query can contain another query - when run the 'included query' is run first.
Possibly therefore you could convert your 'gp_myfunction' into a query and include in this the query to fetch all valid records on which the calcs must be performed.
Just a matter of sorting out the params and conditions.
Alternatively if your 'gp_myfunction' is relatively simple and does not depend on links to other files you can use a CASE statement in the query.
Finally if you are using HF as your data source the the the WL options in the query generator do include 'ExeRun' - not sure how suitable this might be in your case. |
| |
| |
| | | |
|
| | |
| |
Posté le 25 mai 2015 - 14:17 |
Hi,
Another way is perhaps to add you calculation in the "Displaying a row of table xxx" asTable_MyTable_Col_Calculated(table_MyTable.col_param)
The advantage (as I see it), only the rows actually displayed is using time on calculation 
Cheers
Tor-Bjarne |
| |
| |
| | | |
|
| | |
| |
Posté le 25 mai 2015 - 18:40 |
Hmm
Sorry . I donnot get why you want to do that. I my honest opnion you do calculations in sql. SQL is made for that ? Ok if if is verry complex you could do a calculation on as I suggested.
Hmm can you givce some info on what is happening in the procedure. Maybe then I get why you want to do this and maybe even find a work arround.
Happy to help . But please give some more info so I can give it a go.
regards
Allard |
| |
| |
| | | |
|
| | |
| |
Posté le 25 mai 2015 - 18:52 |
Thanks for the responses everyone.
The query I'm trying to write would be an input to a Pivot Table (which means I cannot use the "Displaying a row" idea I;'m afraid.
The function I have written is too long to duplicate in the CASE WHEN ELSE construct. I suppose in theory it could be written using pure SQL, but I try and avoid this approach in favour of using WD language syntax.
I think the way forward is going to be to write the results of the query to a temporary table, adding in the calculated column within that loop, and then base the pivot table on the temporary table.
Cheers
Reg |
| |
| |
| | | |
|
| | |
| |
Posté le 25 mai 2015 - 19:11 |
Hi,
Quote
I my honest opnion you do calculations in sql. SQL is made for that ?
So is WinDev, - for me SQL is a language to retrive data more than doing math. I do a however a lot of simple calculation in sql, complex calculations I think belong in WinDev, but result can be written back to a field in a table.
What about an array of structures that holds the data and have one extra field for calculation?
After the hexecutequery, do a :
if hexecutequery(qry_myquery,HDefault) = True fileto array(cCalc.Data,qry_myquery) end for each ds of cCalc.data ds.fieldExtra = functionMySum(ds.parametervalue) end
And base you table on the global array?
Cheers
Tor-Bjarne |
| |
| |
| | | |
|
| | |
| |
Posté le 25 mai 2015 - 19:13 |
Hi,
I use the pivot table extensively and I do what you discribe all the time. You could add stuff to a querie using Hadd if you want.
Here is an example bit of code where I add stuff to the querie(qry_kasstroomoverzicht) based on condional stuff on a file, Grootboekmutaties :
IF HReadSeekFirst(Dagboek,Functienr,7) THEN
FOR ALL Grootboekmutaties WHERE "DagkboekID = 7 "+ " and Grootboekmutaties.GrootboekID ="+ grootboekIDvandebankrek
QRY_kasstroomoverzicht.Datum = Grootboekmutaties.Datum
QRY_kasstroomoverzicht.saldoomgekeerd = Grootboekmutaties.Saldo
QRY_kasstroomoverzicht.Inkomsten = "Beginsaldo "
HAdd( QRY_kasstroomoverzicht )
END
END
// berekenen overzicht
PVTCalculateAll(TCD_kasstroom_overzicht) |
| |
| |
| | | |
|
| | |
| |
Posté le 25 mai 2015 - 19:17 |
Some good stuff here guys, thanks.
I did not know it was possible to add records to a Qry Allard, very interesting. I'm guessing I could also execute the query and include an 'empty' column in it, then loop through the query and update the 'empty' column with the result of the function? |
| |
| |
| | | |
|
| | |
| |
Posté le 25 mai 2015 - 19:25 |
You can....
Once you have the query result you can treat it the same as file as far as WD is concerned.
Personally I find it more efficient to go down the route suggested by Tor-Bjarne of using an array of structures (or of the data file itself) as this adds a lot more flexibility.
As always with WD there are a number of options. |
| |
| |
| | | |
|
| | |
| |
Posté le 25 mai 2015 - 20:32 |
I'm very much obliged guys, thanks for the help.
Cheers
Reg :rp: |
| |
| |
| | | |
|
| | |
| |
Posté le 26 mai 2015 - 04:22 |
I'm sure there are many ways to accomplish this. I use SPs from within queries using the query editor. This allows for complex conditional calculated items using WL per row.
For me, I like the fact that code can be contained within a SP and called as an item in a query. Results are calculated by HFCS and passed back as query item. Heavy lifting of the calculations is done by the HFCS server rather than the client (WD or WB). This also allows the code to be normalize as to not duplicate across more than one query.
Assuming your SP are stored in your analysis, the in query editor, select new calculated field, select the SP radio and SPs in your analysis are listed. Select the SP for the item, and if you need to you can pass parameters from data. SQL would look something like this:
SELECT Name AS Name,
Amount as Amount,
Set_[YourSetName].spWLCalcPerCap(Customer.Amount,Customer.CapCode) AS PerCap
FROM Customer
Change [YourSetName] to what every you called it when you setup SPs in the Analysis. This will be auto configured if you use the query editor. The example above, the spWLCalcPerCap stored procedure accepts two parameters, Amount and CapCode which values are passed from row data. A practice I use is to prefix SP names with lower case "sp" for readability.
If you get over zelus with SPs in query items, you can grind a HFSC down when many users execute inefficient SPs in a query. So write your SPs accordingly.. If your HFCS is over a VPN or alike, this approach can have significant performance improvement if your SPs refer to other tables in your DB or where joins with other queries or tables don't fit.
Hope this helps... |
| |
| |
| | | |
|
| | |
| |
Posté le 26 mai 2015 - 17:26 |
| |
| |
| | | |
|
| | |
| |
Posté le 05 mai 2020 - 05:34 |
It is possible to execute a procedure that performs a calculation, and the result integrates an Item of the query, but ...
"the procedure must be stored in the analysis, in stored procedures."
This is the only way it works.
I have version 19, maybe there are improvements in the new versions, but I think not.
This is an automatic translation.
From Argentina |
| |
| |
| | | |
|
| | |