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;