SQL functions

The applies to class for the SQL function is Embed-UserFunction, which distinguishes this function from other Rule-Alias-Function instances. In the App Explorer, you can filter the Embed-UserFunction class to view only SQL functions.

For example, as a Lead System Architect (LSA) responsible for enhancing MDC's Delivery Service application, you must capture every truck that performs well during a delivery. The City Manager rates the performance of the truck by entering a number between zero and ten, representing the minimum and maximum net promoter score (NPS). The range of values is divided into the following categories:

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

After capturing the NPS, MDC requests that you define a report that generates output similar to the example in the following table. The vendor should group the report.

Do not use Decision Rules to precompute the category. This approach is not optimal for the following reasons:

• Creates data redundancy, which makes it difficult to keep the data accurate when the original value changes.
• Inhibits “what if” analysis.

You can use other ways to produce this output without using Decision Rules:

• Use a custom SQL function (the SUM function) three times to determine whether the NPS falls within a certain range.
• Use a parameterized subreport (the COUNT function) three times to count whether the NPS falls within a certain range.

Recommended approach A

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

The report labels each column according to the categories that it represents (such as Promoters, Passives, or Detractors). 

Steps

1. The MDC enterprise layer defines an abstract class MDC-Data-Feedback class in the MDC Ruleset. The Delivery Service team uses pattern inheritance to extend the abstract MDC-Data-Feedback class to a concrete MDC-Data-Feedback-Delivery class. The Booking team creates the MDC-Data-Feedback-Delivery class within the Delivery Service Ruleset that the Delivery Service application owns.
   
   The MDC-Data-Feedback-Delivery class contains the TruckRequestRef property, the value of which is equal to the pyID value of the MDC-DS-Work-TruckRequest case.
2. To maximize reuse potential, define a report that uses the custom SQL function in MDC-Data-Feedback-Delivery, instead of MDC-DS-Work-TruckRequest.
   
   The report definition INNER JOINs MDC-Data-Feedback-Delivery to MDC-DS-Work-TruckRequest . The inner join condition is .TruckRequestRef = TruckRequest.pyID, with TruckRequest as the join alias. 
3. The report definition also INNER JOINs the MDC-DS-Work-TruckRequest to MDC-Data-TruckRequest. The inner join condition uses .TruckRequestId= TruckRequestData.pyGUID, with TruckRequest as the join alias. 
4. The report definition is GROUPED BY Vendor.Name, and the remaining columns in the report use aggregate functions, the first being COUNT TruckRequest.pyID.
5. The three aggregate columns on the right use the SUM() function. 
6. Ensure that the input to each SUM() function is the custom SQL function. The first argument to that custom SQL function is the MDC-Data-Feedback .NetPromoterScore property. The second and third arguments to the custom SQL function are literal values, such as 9 and 10, or values that the JOIN function to the reference table obtains.

Recommended approach B

An alternative to approach A is to use a SELECT subquery to retrieve the start and end range values from a grouping of live data rows, as shown in the following example:

This sample subquery assumes that a reference table is defined with the RANGE_GROUP, RANGE_NAME, START, and END column names, as shown in the following example:

Check your knowledge with the following interaction: