With this lesson the content gets slightly more complex :oops: . And I have to admit: In a way more experimental as it is pretty uncovered ground.
Groupings
You probably know the term and the meaning from SQL-queries, so you can guess where we are heading to. For full usage of groupings aggregations are required also, but let us begin with basic groupings as with the following easy example:
~*/SearchResFlightRel/FlightBookRel$(@AGENCYNUM,UPPER(@SMOKER))
Code Example 37,$(@AGENCYNUM,UPPER(@SMOKER)), basic groupings
The $ is now attributed with a list of group elements (agencynum and smoker). Please note that the group element is not necessarily an attribute, any expression is valid. New entries are written to the result table only in case the combination of group elements in the actual data set did not already appear in the result table yet. The result table will contain the first entry of every group. In the chosen example a list of 4116 entries is condensed to about 70 entries.
The standard behaviour of the grouping adds the grouping conditions to the result table automatically. The naming of the group conditions follows the following rules:
- if a the condition consists of an unchanged attribute, the name of the attribute is taken
- if an attribute is used but changed by the usage of a function or an operator, but without usage of a second attribute or a target, the attribute name will be preceeded by a "F_".
- in all other cases name will be EXPR_i. i is the number of the respective condition.
In the examples above the two attribute would be named AGENCYNUM and F_SMOKER.
In some cases it might be required to suppress the automatic adding of the group condition (e.g. it is added with different naming manually in the init part, see next chapter). This is possible with an exclamation mark after the dollar as in the following example:
~*/SearchResFlightRel/FlightBookRel$!(@AGENCYNUM,@SMOKER){!DEMO=@AGENCYNUM&@SMOKER}
Code Example 38,$!(@AGENCYNUM,UPPER(@SMOKER)), basic grouping without group condition takeover
Grouping with assignments (hand-made aggregations)
The grouping syntax is now extended with the possibilities to build assignments based on the group data.
The syntax is as follows: $(ListGroupIdentifier:Initblock:Loopblock)
- ListGroupIdentifier is a non-empty comma separated list of expressions defining the groups as described in the chapter before.
- Initblock is a block with assignments (separated with ; ) processed with the first element of every group. It is usually used to initialize target variables. The initblock may be empty (the two doublepoints have to be stated nevertheless).
- Loopblock is a non-empty block with assignments processed with every element of a group.
- Any used target variable in one of the two group assignment blocks refers to the "group" element.
- As with normal assignments, an assignment might consist of an attribute only, abbreviating an assignment to a target with the same name. Since it is natural to have grouping attributes in the result, it may be added to the init block as in the example below.
- Of course it is also possible to sort the result on any of the fields, including the ones built by aggregation.
The following example:
~*[!AVG]/SearchResFlightRel/FlightBookRel$(@FORCURKEY:!Sum=0;!Amt=0:!Sum=!Sum+1;!Amt=!Amt+@LOCCURAM;!AVG=!Amt%!Sum)
Code Example 39,$(@FORCURKEY:!X=0:!X=!X+1), hand made grouping aggregations
groups the bookings according to the foreign currency and calculates the number of bookings in this curreny, the complete amount (in USD) and the average amount (also in USD). Please note that !AVG never appears on the right side of assignment hence it must not be initialized.
Limitations:
- sub() calls in the grouping assignments are not supported
- dereferencing in grouping assingments may lead to problems
- the syntax as above looks a bit over-crowded and too complex. It is targeted to solve this problem with standard aggregations without the need of initializations and incremental coding.
- The usage of the GET() function is not sufficient in the loop block since it initializes only once.
Group to structure
~*/SearchResFlightRel/FlightBookRel$(*:!Sum=0:!Sum=!Sum+1)
Code Example 40,$(*:!X=0:!X=!X+1), group to a structure
As shown in the example above it is also possible to group the complete content to one row. There is a technical difference compared to the other examples, since the result here is a structure with the field sum, and not a table.
Note: the original example here tried to group to an INT2 field (see unstructured returns). Unfortunately, this is not working since it is not possible to access unstructured returns for reading, which is required for aggregations.
There is the plan to exploit this feature in future together with an enhanced sub()-handling.
Some new functions
List()
With the work on groups I realized that the way to realize enumerations is rather unhandy. Consequently I decided to spend a new feature aiming to assist the user in generating lists.
List has the following syntax: LIST(target,separator,content).
See also the example, note that the := assignment is required here:
~STRING/SearchResFlightRel/FlightBookRel{!Ret:=LIST(!Ret,",",@PASSNAME)}$(*)
Code Example 41,{!X:=List(!X,",",@Feld)}, LIST function
The list function has the following characteristics:
- The separator separates two entries, it will not appear as first or last character.
- As like the GET() function, the first parameter may not be available. If !X is not existing it is defaulted with an empty string value.
- LIST considers empty fields (in the example @PASSNAME is "" ) as empty and they will not be added to the list.
Round() and Int()
Round and Int are using the ABAP round function with the modes ROUND_HALF_UP and ROUND_DOWN. The second parameter - if available - will be handed over as precision.
Log() and Exp()
Returns log respectively exponential value of parameter 1. If second parameter is provided it will be used as basis, otherwise e.
You want to know why the heck one needs logarithm in a business application? Well, I'll show you:
~*/SearchResFlightRel/FlightBookRel$(int(Log(@LOCCURAM,10)):!LMIN=round(exp(int(Log(@LOCCURAM,10)),10),2);!LMAX=round(exp(1+int(Log(@LOCCURAM,10)),10),2):!CNT=Count())
Code Example 42,$(int(Log(@LOCCURAM,10)): Log Function and logged grouping
It looks a bit more complex as it is. Try it out and and take a look on the result, which speaks for itself.Well, to explain it nevertheless. We group to the whole value of the decimal logarithm of our base value. This means nothing else, then the number of digits of the dollar amount. The LMIN and the LMAX value simply calculate the boarders of our area, in our example this is probable [100 .. 1000] and [1000 .. 10000]. Note that the border calculation is part of the init block, hence it will be calculated only one time for every group.The example here might be a bit artificial, nevertheless the concept might be useful to group customers/products/projects where the range is between some Pennys to millions of bucks.
Exists(), Safe() and Initial() and a word on comparisons on data references
Several functions deal with the possibility that a sub function may return no result, i.e. an initial reference. The above methods are designed to catch that problem or react on it.
- exists has one parameter, typically a sub-call. It returns true if the parameter is not an initial reference, i.e. if the sub query returned data
- safe has two parameters, the first one typically a sub-call, the second one a default value. The default value is chosen in case the first parameter is an initial reference. Please distinguish with the GET() functionality which has similar syntax but different semantics.
- initial returns the initial value of the handed over type ('S', 'D', 'B', 'N', 'R', 'O') if set. If the function is used without parameter an initial reference will be returned.
The comparison functions were also enhanced to allow a comparison against the INITIAL-value. Please note the following two points:
- comparisions of references unequal to an initial reference return an undefined result. To the current point the extension is only designed to check against INITIAL, this means at least one parameter should be INITIAL().
- comparisions of values of different type are causing a parser error. Checks like sub(...)<>INITIAL() do only work in case the sub-query returns data references.
That's the content for lesson 9. In respect to lesson 10, I have to say: "no, we are not finished with aggregations. Not at all." :wink:
