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, 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:

1. Create source queries: SQL statements for fetching data (use objects of type DatamartContext.Query);

2. Instantiate a query object using newSqlQuery(), add objects from step 1 to it using addSource() methods;

3. Create 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;

4. Create 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;

5. 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). The 3 steps are following:

1. Create source queries: statements for fetching data (use objects of type DatamartContext.Query);

3. Create SQL statement with JOIN, UNION or other operations for combining the data from step 1;

5. 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 current step is always done agains 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 the special behavior is introduced.

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.