|Table of Contents …||… Table of Code Examples|
|<< Lesson 10. Aggregations|
first of all I have to apologize pretty much, we simply missed out that there was one lesson pending. So here comes lesson 11 after a quite long waiting period.
As suggested before the chapter mainly deals with the sub function. But enough talking, let’s concentrate on the details:
Copy data from sub queries using * = sub(…)
Upto now it was possible to add data returned by a subquery, either if it was a single field or as reference to a structure or a table. In case it was required to add two fields (e.g. Average and Standard Deviation), this would require two distinct sub queries harming performance and readability.
Now it is possible to using the star operator “as target”. In this case the data returned by the sub query is examined. If it is a table or an unstructured field, an error is risen. In case it is a structure all fields of the structure are copied to the result.
Code Example 50,*=SUB(), copy data from sub query
Well ok, the example got a bit complicated. But it is worth taking a look at. On the outer side we have list of flights from which we want to return the keys and some data from the sub query. The inner side / sub query works on the bookings using the “aggregate to one value” feature to ensure that the result is a structure and no table. The sub query filters to Class = Y (Economy Class) and determines the three values Count, Average and Median, speaking for themselves. Interestingly Median is mostly the same, guess that’s caused by the fact, that it is generated data. Please note also that some of the flights have no bookings, hence the fields remain empty / null.
The result of the query is a list of keys plus number, average and median amount (in local currency) of the economy class bookings of the respective flight.
A small remark, in case the complexity above is still not enough for you. I tried to work with a second grouping on the outer level. Actually this should work, except that you do not have the feature you probably want: Access to the data generated within the sub. Every access to a target within the group code is interpreted as access to the group item and not to the actual item.
Addings rows from a sub query using $ = sub() [ the OR feature ]
Sometimes data might be reached on different pathes and it is wishful to return the data from several pathes instead of a distinctive path. Although it was possible with sub queries to return the data either as linked structure or (using dereferencing) in columns, it was not possible to simply “or” the data assembled by the various sub-queries, meaning to add a new row for every dataset found.
This is now possible using the $= feature which realizes kind of OR. For every element returned by the sub query a new row is added and a move-corresponding is executed. This means that no new columns were added by this statement (in contrast to *= ).
See below a non-trivial example of the usage.
Code Example 51,$=SUB(), adding single rows from sub query
Data is collected for the Connection object on two distinct pathes. The first is directly related to the connection and is fetched by the sub-query. The other is executing the relation to the carrier and checks which other connections are available, which are also added to the return data. In fact we are returning data as “my connection” OR “other connections”. In this example case here, the task could be resolved with standard tools also, but there exist typical examples in real life (fetching emails from contacts, which may be placed under contact, relation ship or address details) requiring this feature.
Please note that in above example only a single element is returned by the subquery (no dollar appears in sub query). If we turn the logic around, we have to return and add a full table, so the code would look like:
Code Example 52,$=SUB(…$), adding rows from sub query
Note that the subquery accesses the global variable !1, which is set by the embedding query.
Sub queries can also be used (either returning structures or tables) in this manner, in case the embedding queries returns only the first element (as structure). In this case the inner sub query should also return only the first element, since anyway all others are ignored, but syntactically it would be also supported to return a table.
This enhancement allows to merge structures as in the following self-explanatory example:
<Code Example 53,~STRUCT1+~STRUCT2/./…, merging structures
Code Example 54,!x:=Textsearch(“*”), text search on complete structure
Text search provides the feasibility to search the given pattern on all string fields of the structure which have at least a certain size. The size might be specified as second, optional parameter. If not specified only strings with size 9 or bigger are encountered.
Well I guess with this last feature we are through with our series.
I thank all readers for their interest and the patience as the wholeseries spawned over a pretty long period.
Best Regards, Juergen