SOQL and SOSL Reference

Introduction to SOQL and SOSL
Salesforce Object Query Language (SOQL)
Typographical Conventions in This Document
Quoted String Escape Sequences
Reserved Characters
Alias Notation
SOQL SELECT Syntax
SELECT
TYPEOF
USING SCOPE
WHERE
WITH
GROUP BY
HAVING
ORDER BY
LIMIT
OFFSET
FOR VIEW and and FOR REFERENCE
UPDATE
SET OPTIONS
FOR UPDATE
SOQL SELECT Examples
SOQL SELECT Functions
Relationship Queries
Change the Batch Size in Queries
SOQL Object Limits and Limitations
Syndication Feed SOQL and Mapping Syntax
Location-Based SOQL Queries
Considerations for Querying Currency Data Using SOQL
Salesforce Object Search Language (SOSL)
PDF

SET OPTIONS

Use the SET OPTIONS clause to configure query behavior when querying Data 360 Data Lake Objects (DLOs).

Syntax

1[SET OPTIONS (optionName = optionValue [, optionName = optionValue])]

Example

1SELECT Id, Name
2FROM MyDLO__dlm
3WHERE Status__c = 'Active'
4SET OPTIONS (dataspace = 'default', honorEmptyStrings = true)

dataspace Option

Use dataspace to specify which dataspace to query when querying a DLO. If the dataspace isn’t specified, the query returns zero records. Valid only for DLO queries, not supported for data model object (DMO) queries.

Option Values
dataspace '<dataspaceName>' (string)
Example 
1SELECT AccountId__c, Email__c
2FROM CustomerProfile__dlm
3SET OPTIONS (dataspace = 'default')

honorEmptyStrings Option

Salesforce Platform objects don’t distinguish between NULL and an empty string (''). A SOQL query filtering on either value returns the same records. Data 360 DLOs treat NULL and empty string as different values. Use honorEmptyStrings to control how filtering behaves when querying DLOs.

The honorEmptyStrings option also supports simple queries for DMOs.

Option Values Behavior
honorEmptyStrings true NULL and empty string are treated as different values. Filtering returns different record sets depending on whether the value is NULL or ''.
honorEmptyStrings false (default) NULL and empty string are treated as the same value. Consistent with querying Salesforce Platform objects.
Examples

This query returns records where EmailOptIn__c is either NULL or ''.

1SELECT Id, EmailOptIn__c
2FROM ContactDLO__dlm
3WHERE EmailOptIn__c = ''
4SET OPTIONS (dataspace = 'default', honorEmptyStrings = false)

This query returns only records where EmailOptIn__c is an empty string.

1SELECT Id, EmailOptIn__c
2FROM ContactDLO__dlm
3WHERE EmailOptIn__c = ''
4SET OPTIONS (dataspace = 'default', honorEmptyStrings = true)