...

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.

...

The technique, discussed in this article, may be implemented in two main ways, the . 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 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;

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

5. Run the appropriate executeSqlQuery() method to start execution.

...

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

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

2. –

...

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

2. –

3. 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 agains against the result set returned by the query (queries) from the previous one, to . 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.

...