(Updated Edited template to July 12, 2025.) |
No edit summary |
||
| Line 9: | Line 9: | ||
Example: | Example: | ||
Consultant.PSEvalTuples(self.Assignments->select(a|a.Active)->collect(a|a,a.Consultant,a.BilledHours->select(bh|bh.WorkDate>vTheStartDate).Hours->sum), 100, 0, vTheStartDate) | Consultant.PSEvalTuples(self.Assignments->select(a|a.Active)->collect(a|a,a.Consultant,a.BilledHours->select(bh|bh.WorkDate>vTheStartDate).Hours->sum), 100, 0, vTheStartDate) | ||
The result type is a [[Tuple|Tuple.]] | The result type is a [[Documentation:Tuple|Tuple.]] | ||
The above expression reaches into the database for all Consultants, takes the active assignments, sums the BilledHours that have been reported after <code>vTheStartDate</code> and returns a list of tuples of type {Assignment,Consultant,Part1} where part1 will be the sum of hours. | The above expression reaches into the database for all Consultants, takes the active assignments, sums the BilledHours that have been reported after <code>vTheStartDate</code> and returns a list of tuples of type {Assignment,Consultant,Part1} where part1 will be the sum of hours. | ||
'''Note on limitations''': OCL is a more expressive language than SQL. This leads to situations where something that is easy to express in OCL '''MAY''' '''NOT''' be correctly translated to an equivalent SQL-statement. We know that performing multiple aggregating functions like sum, min, max, avg, and count(*) (in OCL, it will be ->[[OCLOperators minValue|minvalue]], ->[[OCLOperators maxValue|maxvalue]], ->[[OCLOperators average|average]], ->[[OCLOperators size|size]]) in the same tuple for different sets of data will not yield the expected results. Always do a sanity check of the numbers returned from a new expression and test it to verify its efficacy. Example of expression given wrong answer: <code>->collect(x|x,x.Name,x.Something->select(goodone).Value->sum,x.Something->select(badone).Value->sum )</code> workaround - split it in two, one expression per aggregation, | '''Note on limitations''': OCL is a more expressive language than SQL. This leads to situations where something that is easy to express in OCL '''MAY''' '''NOT''' be correctly translated to an equivalent SQL-statement. We know that performing multiple aggregating functions like sum, min, max, avg, and count(*) (in OCL, it will be ->[[Documentation:OCLOperators minValue|minvalue]], ->[[Documentation:OCLOperators maxValue|maxvalue]], ->[[Documentation:OCLOperators average|average]], ->[[Documentation:OCLOperators size|size]]) in the same tuple for different sets of data will not yield the expected results. Always do a sanity check of the numbers returned from a new expression and test it to verify its efficacy. Example of expression given wrong answer: <code>->collect(x|x,x.Name,x.Something->select(goodone).Value->sum,x.Something->select(badone).Value->sum )</code> workaround - split it in two, one expression per aggregation, | ||
Always remember that your SQL-server might also need indexes and other performance settings to execute your expression efficiently. | Always remember that your SQL-server might also need indexes and other performance settings to execute your expression efficiently. | ||
See also: [[Documentation:OCLOperators sqlpassthrough|Sqlpassthrough]], [[OCLOperators PSEvalValue|PSEvalValue]], [[OCLOperators PSEval|PSEval]] | '''See also:''' [[Documentation:OCLOperators sqlpassthrough|Sqlpassthrough]], [[Documentation:OCLOperators PSEvalValue|PSEvalValue]], [[Documentation:OCLOperators PSEval|PSEval]] | ||
{{Edited|July|12|2025}} | {{Edited|July|12|2025}} | ||
[[Category:OCLOperators]] | [[Category:OCLOperators]] | ||
Latest revision as of 05:26, 6 February 2025
The OCLOperator PSEvalTuples makes it possible to use oclPS in any OCL expression. Using this, you can "reach into" the database from OCL and action-language (EAL).
Expression template:
SomeClass.PSEvalTuples(<ps-expression>,maxfetch,offset,<dependon>)
Note! PSEvalTuples doesn't subscribe to sets from DB. To make the operator auto-updating, provide an expression in "dependon" that, when changed, should also rerun the PSEvalTuples expression. This can, for example, be a timer.
Example:
Consultant.PSEvalTuples(self.Assignments->select(a|a.Active)->collect(a|a,a.Consultant,a.BilledHours->select(bh|bh.WorkDate>vTheStartDate).Hours->sum), 100, 0, vTheStartDate)
The result type is a Tuple.
The above expression reaches into the database for all Consultants, takes the active assignments, sums the BilledHours that have been reported after vTheStartDate and returns a list of tuples of type {Assignment,Consultant,Part1} where part1 will be the sum of hours.
Note on limitations: OCL is a more expressive language than SQL. This leads to situations where something that is easy to express in OCL MAY NOT be correctly translated to an equivalent SQL-statement. We know that performing multiple aggregating functions like sum, min, max, avg, and count(*) (in OCL, it will be ->minvalue, ->maxvalue, ->average, ->size) in the same tuple for different sets of data will not yield the expected results. Always do a sanity check of the numbers returned from a new expression and test it to verify its efficacy. Example of expression given wrong answer: ->collect(x|x,x.Name,x.Something->select(goodone).Value->sum,x.Something->select(badone).Value->sum ) workaround - split it in two, one expression per aggregation,
Always remember that your SQL-server might also need indexes and other performance settings to execute your expression efficiently.
See also: Sqlpassthrough, PSEvalValue, PSEval