You are here:
SQL Rules for Insights
When you write SQL for calculated or streaming insights in Data 360, rules govern how each clause is interpreted and what constraints apply. You can also embed a nested subquery in a WHERE or JOIN clause to restrict which rows the outer query returns.
Required Editions
| Available in: All Editions supported by Data 360. See Data 360 edition availability. |
Query Rules by Clause
| Clause | Rule |
|---|---|
| General | DMO names are case-sensitive. Verify that DMO references in your SQL match the casing shown in the Object API Name column on the Data Model tab. |
| A query must contain at least one measure. A measure is any field wrapped in an aggregation function. | |
| Any attribute in the SELECT clause that isn't inside an aggregation function is treated as a dimension and must appear in the GROUP BY clause. | |
| You can't aggregate within an aggregation. | |
| You can't use DISTINCT in a top-level query. | |
You can use multiline comments (/* */). |
|
SELECT |
The alias for a dimension can't match the field's original name. For
example, UnifiedIndividual__dlm.Id__c as customer_id__c is
valid, but UnifiedIndividual__dlm.Id__c as Id__c causes an
error. |
You must use TRY_CONVERT_CURRENCY in the SELECT clause
when your insight includes a currency field. |
|
WHERE |
The WHERE clause can't contain an aggregate expression. |
| The WHERE clause can't reference a dimension or measure alias. | |
GROUP BY |
The GROUP BY clause must be a non-aggregate expression that uses non-aliases. |
| The GROUP BY clause can reference a dimension alias, but the alias can't be part of an expression. | |
| The GROUP BY clause can't include a measure alias or an aggregate function. | |
ORDER BY |
ORDER BY isn't allowed in a top-level SELECT statement. |
RANK |
PARTITION BY and ORDER BY inside a RANK function can be aggregate or non-aggregate expressions. |
| A RANK clause that contains an aggregate expression is treated as a measure. | |
| Measures produced by RANK are non-aggregatable. You must provide all defined dimensions when querying them. | |
CASE |
A CASE expression that contains an aggregate function is treated as a measure. |
Measures produced by CASE(WHEN <aggregate>) are
non-aggregatable. You must provide all defined dimensions when querying
them. |
|
To compare the results of aggregate functions, use a CASE WHEN statement.
Boolean comparison doesn't support aggregate functions. For example, instead of
WHEN SUM(amount) > 100, use CASE WHEN SUM(amount) >
100 THEN ... END. |
|
| Date fields | When filtering for an empty string in a date field, use a null value instead. |
Nested Subqueries
A subquery is a query embedded inside another query, typically in a WHERE or JOIN clause. Use a subquery to return data that restricts which rows the outer query retrieves.
For example, this query averages sales order totals for customers who were born after 1980.
SELECT
avg(SalesOrder__dlm.GrandTotalAmount__c) as avg__c,
SubQuery1.Id as customer_id__c
FROM SalesOrder__dlm
JOIN (
SELECT Individual__dlm.Id__c as Id
FROM Individual__dlm
WHERE year(Individual__dlm.BirthDate__c) > 1980
) as SubQuery1
ON SalesOrder__dlm.SoldToCustomerId__c = SubQuery1.Id
GROUP BY customer_id__c;Did this article solve your issue?
Let us know so we can improve!
