Introduction

Pricefx API for Groovy logics offers different ways to leverage full potential of SQL. One of these ways is a query technique, that is connected with the use of the following methods:

DatamartContext.SqlQuery newSqlQuery()
DatamartContext.SqlQuery addSource(DatamartContext.Query sourceQuery)
DatamartContext.SqlQuery addSource(DatamartContext.Query sourceQuery, String alias)
DatamartContext.SqlQuery addWith(String withClause, Object... bindings)
DatamartContext.SqlQuery addWith(String withClause, String alias, Object... bindings)
DatamartContext.SqlQuery setQuery(String sql, Object... bindings
Matrix2D executeSqlQuery(DatamartContext.SqlQuery sqlQuery)
Matrix2D executeSqlQuery(String sql, Object... sources)

This technique enables you to chain multiple operations together, and the database will process them during one call. Using it, you can expect the result to be much quicker than querying the database multiple times. The description of the basic use of these methods and its specifics is the main subject of this article.

Structure

More Complex

The technique, discussed in this article, may be implemented in two main ways. The more complex way involves executeSqlQuery(DatamartContext.SqlQuery sqlQuery) method and includes 5 steps:

Create source queries: SQL statements for fetching data (use objects of type DatamartContext.Query);
Instantiate a query object using newSqlQuery(), add objects from step 1 to it using addSource() methods;
Create an SQL statement with JOIN, UNION or other operations for combining the data from step 1, and add it to the query object from step 2 using addWith() methods;
Create an SQL statement with aggregation that will be applied to the result of statement execution from the step 3, and add it to the query object from step 2 using setQuery() method;
Run the appropriate executeSqlQuery() method to start execution.

The use of addWith() method is optional, it provides the user with an additional intermediate stage for data manipulation.

def ctx = api.getDatamartContext()
     def dm = ctx.getDatamart("TransactionsDM")
     def ds = ctx.getDataSource("ForecastDS")
     
     // STEP 1
     def t1 = ctx.newQuery(dm)
                 .select("ProductID", "product")
                 .select("ProductGroup", "pg")
                 .select("SUM(InvoicePrice)", "revenue")
                 .select("SUM(Quantity)", "volume")

     def t2 = ctx.newQuery(ds, false)
                  .select("ProductID", "product")
                  .select("Revenue, "revenue")
                  .select("Volume", "volume")
     // STEP 2
     def sqlQuery = ctx.newSqlQuery()
                       .addSource(t1)
                       .addSource(t2)
      
     // STEP 3
     def with = """ SELECT T1.product, T1.revenue AS ActualRevenue, T2.revenue AS ForecastRevenue
                           T1.volume AS ActualVolume, T2.volume AS ForecastVolume
                           FROM T1 LEFT OUTER JOIN T2 USING (product)
                           WHERE T1.PG = ? """
     sqlQuery.addWith(with, "PG-ABC")
     
     // STEP 4
     def sql = " SELECT SUM(ActualRevenue) - SUM(ForecastRevenue) FROM T3 "
     sqlQuery.setQuery(sql)
     
     // STEP 5
     return ctx.executeSqlQuery(sqlQuery)?.toResultMatrix()

Less Complex

The less complex way involves the usage of only one method from our list: executeSqlQuery(String sql, Object... sources). Compared to the more complex procedure, it contains only 3 steps:

Create source queries: statements for fetching data (use objects of type DatamartContext.Query);
–
Create an SQL statement with JOIN, UNION or other operations for combining the data from step 1;
–
Run the appropriate executeSqlQuery() method to start execution.

def ctx = api.getDatamartContext()
def dm = ctx.getDatamart("TransactionsDM")
def ds = ctx.getDataSource("ForecastDS")

// STEP 1
def t1 = ctx.newQuery(dm)
t1.select("ProductID", "product")
t1.select("SUM(InvoicePrice)", "revenue")
t1.select("SUM(Quantity)", "volume")

def t2 = ctx.newQuery(ds, false)
t1.select("ProductID", "product")
t2.select("Revenue, "revenue")
t2.select("Volume", "volume")

// STEP 3
def sql = """ SELECT T1.product, T1.revenue AS ActualRevenue, T2.revenue AS ForecastRevenue
                     T1.volume AS ActualVolume, T2.volume AS ForecastVolume
                     FROM T1 LEFT OUTER JOIN T2 USING (product) """
// STEP 5
return ctx.executeSqlQuery(sql, t1, t2)?.toResultMatrix()

Important Notes

Attribute Letter Case

The most important aspect that should be taken into consideration while using this technique is the attribute letter case behavior. When you use this technique, a SELECT statement is constructed on the server side combining all the user statements together. During this process, the query at the current step is always done against the result set returned by the query (queries) from the previous one. To minimize the amount of possible errors caused by the difference in attribute letter case, special behavior is introduced.

In Step 1 above: All the attributes in the result set from executing the statements here will be switched to lower case, even if:

the field names from the source tables had upper case letters,
the aliases for the fields in SELECT statement were quoted.

In Step 3 above: Standard SQL behavior can be expected at this stage, everything in the user SELECT statement will be switched to lower case, unless it is quoted.

In Step 4 above: Standard SQL behavior can be expected at this stage as well.

The use of the * operator will preserve the attribute letter case from the input data.
The use of double quotes on an attribute will preserve its letter case, all the unquoted attributes will be switched to lower case to increase compatibility.
More errors may appear at this step, because of the presence of attributes with different letter cases in user SELECT statement and in the result set from previous step.

Study the example to make sure that you understand this concept . There you can see which attributes will be switched to lower case, and for which the case will be preserved.

Image 1. Case switching example (orange = to lower case; green = case preserved).

Configuration Engineer Knowledge Base

Use Complex Queries