SQL functions

The applies to class for the SQL Function is Embed-UserFunction, which makes it different than other Rule-Alias-Function instances. In the App Explorer, you can filter on the Embed-UserFunction class to see only SQL Functions.

Suppose you are a lead system architect (LSA) helping FSG enhance its Booking application. You are tasked with capturing each customer’s opinion of how well FSG hosted an event at a certain venue. The customer is asked to rate FSG’s performance by entering a number between zero and ten. The numbers zero and ten represent the minimum and maximum net promoter score (NPS). This range of values consists of the three categories:

• 0 – 6: Detractors
• 7 – 8: Passives
• 9-10: Promoters

After capturing the net promoter score, FSG wants you to define a report that produces output like the example shown below. The report is supposed to be grouped by Venue

Using decision rules to pre-compute the category is a poor choice for multiple reasons.

• Data redundancy; suppose the original value is updated
• Inhibits “what if” analysis

There are other ways to produce this output without using decision rules:

1. Use a custom SQL Function to flag whether the NPS falls within a certain range; use the SUM function. Use the SQL Function three times.
2. Use a parameterized subreport to COUNT whether the NPS falls within a certain range. Use the subreport three times.

Recommended approach, v1

The best approach is to define a SQL Function with three integer parameters (such as value, range start, and range end). The SQL Function uses the following code.

CASE WHEN {1} >= {2} AND {1} <= {3} THEN   1

ELSE 0

END

The report labels each column according to the categories that it represents (such as Promoters, Passives, or Detractors). The second and third parameters for the Promoters column are 9 and 10, respectively. 

Steps

1. The FSG COE has defined an abstract FSG-Data-Feedback class in the FSG ruleset. The Booking team uses pattern inheritance to extend the abstract FSG-Data-Feedback class to a concrete class named FSG-Data-Feedback-Event. The Booking team creates the FSG-Data-Feedback-Event class within the BookEvent ruleset which is owned by the Booking application.
2. The FSG-Data-Feedback-Event class contains an EventRef property, the value of which is set equal to an FSG-Booking-Work-BookEvent case's pyID.
3. To maximize reuse potential, the custom SQL Function-utilizing report definition is defined in FSG-Data-Feedback-Event, not FSG-Booking-Work-BookEvent.
4. The report definition INNER JOINs FSG-Data-Feedback-Event to FSG-Booking-Work-BookEvent WHERE .EventRef =  EVENT.pyID, "EVENT" being a join alias.
5. The report definition also INNER JOINs the  FSG-Booking-Work-BookEvent to FSG-Data-Venue, using .EVENT.VenueGUID = VENUE.pyGUID, "VENUE" being a join alias.
6. The report definition GROUP BY VENUE.Name, the remaining columns in the report using aggregate functions, the first being COUNT EVENT.pyID.
7. The three aggregate columns on the right uses the SUM() function,
8. The input to each SUM() function is the custom SQL Function described above. The first argument to that custom SQL Function is the FSG-Data-Feedback .NetPromoterScore property. The second and third arguments to the custom SQL Function are literal values, such as 9 and 10, or values obtained by a JOIN to the reference table described above.

Recommended approach, v2

An alternative to v1 above is to use a SELECT subquery to retrieve the start and end range values from a grouping of Live Data rows as shown below.

CASE WHEN {1} >= (select RANGE_START from RANGE_VALUES where RANGE_NAME = {2} AND RANGE_GROUP = {3})

             AND {1} <= (select RANGE_END from RANGE_VALUES where RANGE_NAME = {2} AND RANGE_GROUP = {3})

THEN  1

ELSE 0

END

Above subquery assumes that a reference table has been defined with RANGE_GROUP, RANGE_NAME, START, and END column names.

Alternative approach

This approach is not recommended.

Within the alternate solution each subreport needs to use an OUTER JOIN to work properly. Obviously, a report that requires three OUTER JOINed subqueries is less performant than the SQL generated by the SQL Function approach. The reason is that the SQL Function is straightforward as each queried row is examined, simple comparison logic is used in each of the three categories to output a zero or a one. Simultaneously, the SUM aggregating function adds the output of the case to its running total for each unique GROUP BY combination, here, just the Venue name.

On the other hand, the subreport approach requires that the GROUP BY in the main report where he total number of events is counted must also "tack on" the counts from three OUTER JOINed subqueries. This is more complex for the database to achieve, hence is less performant.