Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
16
Erster Beitrag:
vor 2 Jahren, 9 Monaten
Letzter Beitrag:
vor 2 Jahren, 9 Monaten
Beteiligte Autoren:
SolutionJ-Reg, Allard, Tor-Bjarne, DerekT, Davie

Calling procedure from Query

Startbeitrag von SolutionJ-Reg am 24.05.2015 12:19

WD18

Hi,

Is there a way of calling a global procedure from a Query in order to create a calculated item?

Cheers
Reg

Antworten:

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)

von Allard - am 24.05.2015 13:26
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.

von SolutionJ-Reg - am 24.05.2015 13:29
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.

von Allard - am 25.05.2015 11:11
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)"

von SolutionJ-Reg - am 25.05.2015 11:17
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.

von DerekT - am 25.05.2015 12:05
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

von Tor-Bjarne - am 25.05.2015 12:17
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

von Allard - am 25.05.2015 16:40
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

von SolutionJ-Reg - am 25.05.2015 16:52
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

von Tor-Bjarne - am 25.05.2015 17:11
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)

von Allard - am 25.05.2015 17:13
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?

von SolutionJ-Reg - am 25.05.2015 17:17
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.

von DerekT - am 25.05.2015 17:25
I'm very much obliged guys, thanks for the help.

Cheers
Reg :rp:

von SolutionJ-Reg - am 25.05.2015 18:32
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...

von Davie - am 26.05.2015 02:22
Thanks Mr Black...

von SolutionJ-Reg - am 26.05.2015 15:26
Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.