When you query Analytics tables (e.g., Datamart, Data Source), you’re using PA Query API. This PA Query API should be used in all scenarios, whenever possible.

Sometimes you encounter special situation where PA SQL Query API can be the only solution to deal with the problem which you cannot solve by PA Query API; for example:

You need to do various types of JOINs between tables, possibly with special JOIN conditions.

Remember, simple LEFT JOINS of two Data Sources can be solved using a Datamart, using the common way of building it, i.e., that 1^st Data Source will be the primary one (the one which supplies also key columns), and the columns of the 2^nd Data Source will be used/picked up to the same Datamart. See documentation about Datamart for more details.

Usage of more complex functions/expressions (e.g., LAG()).
When something would have to be simulated in the Groovy code (e.g., doing two separate lookups and then join data in the Groovy code), but can be done by PA SQL directly and with better performance.
You need special constructs like SELECT FROM SELECT.

Think twice before using PA SQL Query API. Always ensure that you’ve designed your tables properly, to be well suited for solution of the problem before you start doing heavy PA SQLs on them. No special query will help if your tables' design does not fit the solution of the problem.

PA SQL Query

Is a query written using ANSI SQL, which is built on top of one or more "views" (kind of abstraction of the underlying source of data). You can use them to solve tasks, which are not possible to do with PA Query API.

Concept of "Views"

Because Pricefx backend is multi-tenant and the backend must ensure that you cannot read data of another tenant, the PA SQL queries cannot let you to read directly from actual underlying DB tables but instead they let you read data from abstraction of the underlying source of data, which we call here "views" (this does NOT mean a database view).

The "view" can be constructed by creation of a Query via PA Query API.

Figure 1. Sample PA SQL query and it's 2 views

From technical perspective, the "view" is implemented as Common Table Expressions but this could possibly change in the future, without any need for modification of logics.

For the Tx views, you can use exactly the same source as for PA Queries, i.e.:

Data Feed
Data Source
Datamart
DMTable (used in PriceOptimizer)

PA SQL Example

In the following example you can see that we’re defining two queries - one to Datamart and one to Data Source - which are then in the SQL used as views and JOINed together.

This example is not possible to do by built-in capabilities of Datamart itself, because the JOIN is using a date-range.

Note: Wherever you see the dots "…..", we removed some parts for easier readability.

Sample of PA SQL Query with 2 views JOINed together

def ctx = api.getDatamartContext()

def dm1 = ctx.getDatamart("Transaction")
def q1 = ctx.newQuery(dm1)                                          //❶
        .select("Currency")
        .select("InvoiceDate")
.....
        .where(Filter.equal("CountryCode", input.CountryCode))
        .where(Filter.equal("ProductId", input.ProductId))

def dm2 = ctx.getDataSource("ProductCompetitionData")
def q2 = ctx.newQuery(dm2)                                          //❷
        .select("ProductId")
        .select("CountryCode")
.....

def sql = '''                                                       //❸
SELECT
    T1.CountryCode        AS "CountryCode",
    AVG( T1.InvoicePrice / T1.Quantity )  AS "InvoicePricePerUnit" ,
    AVG(T2.Price)         AS "CompetitorPricePerUnit",
    T2.Currency           AS "CompetitorPriceCurrency"
    .....

FROM T1
LEFT JOIN T2

    ON  T1.ProductId   = T2.ProductId
        AND  T1.CountryCode = T2.CountryCode
        AND  T2.ValidFrom <= T1.InvoiceDate  AND  T1.InvoiceDate < T2.ValidTo

GROUP BY
    T1.CountryCode,
    T1.ProductId,
    .....
ORDER BY
    T1.CountryCode,
    T1.ProductId,
    .....
'''

def result = ctx.executeSqlQuery(sql, q1, q2)                       //❹

return result ? result.toResultMatrix() : null

❶ Query to a Datamart, which will be used in the SQL as T1.
❷ Query to a DataSource, which will be used in the SQL as T2.
❸ The SQL, which references the 2 views as T1 and T2.
❹ This is the function, which puts it all together.The PA Query supplied in 2^nd parameter will be then available in the SQL query as T1, the query in 3^rd parameter will be T2, etc.

Comparison with PA Query API

The following quick comparison should give you an idea, when you might need to use PA SQL instead of PA Query API. You can see that many standard situations can be solved by Query API.

Feature	PA Query API	PA SQL Query API

Feature	PA Query API	PA SQL Query API
SELECT	+	+
DISTINCT	+	+
FROM	+	+
WHERE	+	+
GROUP BY	+	+
HAVING	+	+
ORDER BY	+	+
JOIN	only via Datamart	+
SELECT FROM SELECT	-	+
UNION	-	+
WHERE __ IN (SELECT…	-	+
some complex functions	-	+

Studio’s PA Console

PA Console is a tool in Studio, which simplifies design and development of Analytics queries using SQL.

You can use it to quickly prototype initial basic queries and then modify them and test them before copying the final code to the Logic.

Figure 2. Studio's PA Console tool in IntelliJ

References

Studio

PA SQL Console
Keyboard Shortcuts, including those for PA SQL Console

Documentation

Datamart / Analytics Functions - example of OUTER JOIN of a DM with a DS
UI - Datamart as a Join of several DataSources
Comparison of Product Extensions and Data Sources / Datamart
Query Data Marts - contains example how to use the in-memory database and also reference of it’s API
Filters for Data Reading
Datamart Query Expressions

Configuration Engineer Knowledge Base

Advanced Data Source Queries