Loading
Feature degradation | Gmail Email delivery failureRead More
About Salesforce Data 360
Table of Contents
Filter by (0)Add
Select Filters

          No results
          No results
          Here are some search tips

          Check the spelling of your keywords.
          Use more general search terms.
          Select fewer filters to broaden your search.

            Search all of Salesforce Help
            Search all of Salesforce Help
            SQL Rules for Insights

            You are here:

            1. Salesforce Help
            2. Docs
            3. Data 360

            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.
            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;
             
            Loading
            Salesforce Help | Article