Selecting Data using SQL Functions

SQL has many built-in functions for performing calculations on data.

Useful aggregate functions:

• AVG() - Returns the average value
• COUNT() - Returns the number of rows
• FIRST() - Returns the first value
• LAST() - Returns the last value
• MAX() - Returns the largest value
• MIN() - Returns the smallest value
• SUM() - Returns the sum

The Accelerator supports this by defining the function expression on the AB_DataSourcePropertyReference class within the DataMaps am_LoadFieldMaps method.

For Example, I have an Order Item file that has a Product ID (YD1I1PID) field and a Unit Price (YD1IPRUN) field. For demonstration purposes, I want to sum the Unit Price for each unique Product ID.  To do this I need to Group_by Product ID and use the SUM function on the Unit Price. 

We first need to add a property to the Order Item Entity that will hold the Total Price value which will contain the SUM of the Unit Price.

[Display(Name = "TOTALPRICE", ResourceType = typeof(DescriptionResource))]
[AB_Length(8)]
[DataMember]
public decimal? TotalPrice //Map Field: YD1IPRUN
  {
      get { return am_GetPropertyValue(TotalPriceProperty); }
      set { am_SetPropertyValue(TotalPriceProperty, value); }
  }
public static AB_PropertyMetadata<decimal?> TotalPriceProperty = am_CreatePropertyMetaData<decimal?>("TotalPrice", DescriptionResource.TOTALPRICE, null);

Next, we need to add a data map which will contain the SQL function and map the result into the TotalPrice property. Add a map as you normally would, but receive the result into a variable. In this case I am receiving the Type AB_DataSourcePropertyReference into the variable "sum".  Then you can set the property ap_FunctionExpression with  the SQL function you want to use. Notice that I also added a data category "YD1I_TotalsByProduct". I only want to select this field and do the sum when using this data catagory. 

var sum = maps.am_AddDataMap("TotalPrice", OrderItemEntity.TotalPriceProperty, dataCategories: new List<string> { "YD1I_TotalsByProduct" });
sum.ap_FunctionExpression = "SUM(YD1IPRUN) as \"TotalPrice\"";

The data category "YD1I_TotalsByProduct" was also added to the Product Internal ID which is the field I want to group by. You will notice that I also have the data category "*ALL" which means this field will always be selected if no category is applied.

maps.am_AddDataMap("YD1I1PID", OrderItemEntity.ProductInternalIDProperty, dataCategories: new List<string> { "*ALL", "YD1I_TotalsByProduct" });

The last step is to add the Group_By and Category.  The Group By will return a single record per Product ID and the Category allows me to select only certain fields (In this case Product Internal ID and Total Price). We can do this on the ap_Query object which is passed with the AB_SelectInputArgs.  I generally like to add the Group By and Category in the am_Select of the Business Process class and condition it based on an Accelerator view. You will also notice that I am setting the ap_RecordsPerRequest and the ap_MaxCount to a max value. When running this query we need to return all records as Next Previous is not supported with this Group_BY.

public override AB_SelectReturnArgs am_Select(AB_SelectInputArgs inputArgs)
{
    if (inputArgs.ap_View == "YD1I_TotalsByProduct")
            {
                inputArgs.ap_Query.am_AddGroupBy(OrderItemDataEntity.OrderItemEntity.ProductInternalIDProperty);
                inputArgs.ap_Category = inputArgs.ap_View;
                inputArgs.ap_RecordsPerRequest  = int.MaxValue;
                inputArgs.ap_MaxCount = int.MaxValue;
            }

        var retArgs = base.am_Select(inputArgs);
        return retArgs;
}