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.

1[TYPEOF typeOfField
2        {WHEN whenObjectType THEN whenFieldList}[...]
3        [ELSE elseFieldList]
4    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:

1SELECT Name FROM Account
2WHERE CreatedById IN
3    (
4    SELECT
5        TYPEOF Owner
6            WHEN User THEN Id
7            WHEN Group THEN CreatedById
8        END
9    FROM CASE
10    )

The following example is valid because TYPEOF is only used in the outer SELECT clause:

1SELECT
2    TYPEOF What
3        WHEN Account THEN Phone
4        ELSE Name
5    END
6FROM Event
7WHERE CreatedById IN
8    (
9    SELECT CreatedById
10    FROM Case
11    )

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.

1SELECT
2 TYPEOF What
3  WHEN Account THEN Id, FORMAT(LastModifiedDate) LastModifiedDate__f
4  WHEN Opportunity THEN Id
5 END
6FROM Task

Instead, run the same query without functions to retrieve a list of IDs.

1SELECT
2    TYPEOF What
3        WHEN Account THEN Id, LastModifiedDate
4        WHEN Opportunity THEN Id
5    END
6FROM Task

Then, run a second query with functions on the resulting ID list.

1SELECT
2    FORMAT(LastModifiedDate) LastModifiedDate__f
3FROM Account
4WHERE 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.

1SELECT
2  TYPEOF What
3    WHEN Account THEN Phone, NumberOfEmployees
4    WHEN Opportunity THEN Amount, CloseDate
5    ELSE Name, Email
6  END
7FROM Event

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