SOQL and SOSL Reference

Clouds with binary code floating aboveCloud with binary code floating above

No Results

Search Tips:

  • Please consider misspellings
  • Try different search keywords
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 FOR REFERENCE
UPDATE
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
Salesforce Object Search Language (SOSL)
PDF

TYPEOF

TYPEOF is an optional clause that can be used in a SELECT statement of a SOQL query when you’re querying data that contains polymorphic relationships. A TYPEOF expression specifies a set of fields to select that depend on the runtime type of the polymorphic reference.

TYPEOF is available in API version 46.0 and later. It is also available in earlier versions as part of a Developer Preview of the SOQL Polymorphism feature.

[TYPEOF typeOfField
        {WHEN whenObjectType THEN whenFieldList}[...]
        [ELSE elseFieldList]
    END][...]

If you need to query multiple polymorphic relationship fields, you can use more than one TYPEOF expression in a single SELECT statement.

You can provide as many WHEN clauses as needed, one per object type. The ELSE clause is optional and used if the object type for the polymorphic relationship field in the current record doesn’t match any of the object types in the provided WHEN clauses. The syntax specific to TYPEOF is as follows.

Syntax Description
typeOfField A polymorphic relationship field in objectType or a polymorphic field in a parent of objectType that can reference multiple object types. typeOfField cannot reference a relationship field that is also referenced in the fieldList of a SELECT statement.
whenObjectType An object type for the given WHEN clause. When the SELECT statement runs, each object type associated with the polymorphic relationship field specified in the typeOfField expression is checked for a matching object type in a WHEN clause.
whenFieldList A list of one or more fields, separated by commas, that you want to retrieve from the specified whenObjectType. These are fields in the referenced object type or paths to related object fields, not fields in the primary object type for the SELECT statement.
elseFieldList A list of one or more fields, separated by commas, that you want to retrieve if none of the WHEN clauses match the object type associated with the polymorphic relationship field specified in typeOfField. This list may only contain fields valid for the Name object type, or paths to related object fields in Name.
Note the following considerations for using TYPEOF:
  • TYPEOF can’t be used with a relationship field whose namePointing attribute is false.
  • TYPEOF can’t be used with a relationship field whose relationshipName attribute is false.
  • TYPEOF is only allowed in the SELECT clause of a query. You can filter on the object type of a polymorphic relationship using the Type qualifier in a WHERE clause. For details, see Filter on Polymorphic Relationship Fields.
  • TYPEOF isn’t allowed in queries that don’t return objects, such as COUNT() .
  • TYPEOF can’t be used in SOQL queries that are the basis of Streaming API PushTopics.
  • TYPEOF can’t be used in SOQL used in Bulk API.
  • TYPEOF expressions can’t be nested. For example, you can’t use TYPEOF inside the WHEN clause of another TYPEOF expression.
  • TYPEOF isn’t allowed in the SELECT clause of a semi-join query. You can use TYPEOF in the SELECT clause of an outer query that contains semi-join queries. The following example is not valid because TYPEOF is used in the semi-join query:
    SELECT Name FROM Account
WHERE CreatedById IN
    (
    SELECT 
        TYPEOF Owner
            WHEN User THEN Id
            WHEN Group THEN CreatedById
        END
    FROM CASE
    )
    The following example is valid because TYPEOF is only used in the outer SELECT clause:
    SELECT 
    TYPEOF What
        WHEN Account THEN Phone
        ELSE Name
    END
FROM Event
WHERE CreatedById IN
    (
    SELECT CreatedById
    FROM Case
    )
  • TYPEOF can’t be used in queries with functions in the SELECT clause. The following example is not valid because the TYPEOF includes the FORMAT function.
    SELECT
 TYPEOF What
  WHEN Account THEN Id, FORMAT(LastModifiedDate) LastModifiedDate__f
  WHEN Oppty THEN Id
 END
FROM Task
    Instead, run the same query without functions to retrieve a list of IDs.
    SELECT
    TYPEOF What
        WHEN Account THEN Id, LastModifiedDate
        WHEN Opportunity THEN Id
    END
FROM Task
    Then, run a second query with functions on the resulting ID list.
    SELECT 
    FORMAT(LastModifiedDate) LastModifiedDate__f 
FROM Account 
WHERE Id in RetrievedIdList
  • TYPEOF can’t be used in queries with GROUP BY, GROUP BY ROLLUP, GROUP BY CUBE, and HAVING.
The following example selects specific fields depending on whether the What field of an Event references an Account or Opportunity.
SELECT 
  TYPEOF What
    WHEN Account THEN Phone, NumberOfEmployees
    WHEN Opportunity THEN Amount, CloseDate
    ELSE Name, Email
  END
FROM Event

See Understanding Relationship Fields and Polymorphic Fields for details on polymorphic relationships, and more examples of TYPEOF.