Chat now with support
Chat with Support

Toad Data Point 5.0 - User Guide

Create Salesforce SOQL Queries

Toad uses Salesforce SOQL instead of SQL in Salesforce connections (unless the Salesforce connection is part of a cross-connection query).

When composing a Salesforce query in the Editor, use SOQL. When you build a Salesforce query in the Query Builder, Toad generates a SOQL query in the Query tab.

For more information about Salesforce SOQL, see the Salesforce SOQL Reference document.

Note: Queries written using SQL instead of SOQL are not supported for Salesforce connections. Salesforce queries created in Toad Data Point 3.5 or earlier are not supported.

To use the Query Builder to query Salesforce data

  1. Select Tools | Query Builder | Query Builder.
  2. Drag tables (Salesforce objects) from the Object Explorer to the Diagram pane. Existing relationships display.

    Note: You cannot create new relationships when building a Salesforce SOQL query.

  3. If a pair of tables (objects) has more than one relationship, choose one relationship and remove the others.
    • To add additional existing relationships, add multiple instances of a table.
    • To restore a dropped relationship between tables, right-click the child table and select Restore FK.
  4. Select the columns (Salesforce fields) you want to add to the query. For more information, see Build Queries Visually.
  5. To use a SOQL date range function, select a date column and click in the Where field for that column. Then select a date range from the Date Range tab of the Where Condition dialog.
  6. Click Execute SQL to run the query.

    Note: Not all SOQL functions and clauses are supported by the Query Builder. See Considerations and Limitations for a list of limitations.

To use the Editor to query Salesforce data

  1. Select Tools | Edit | SQL Editor.
  2. Use code completion to select tables (objects) and columns (fields). You can also use code completion to select the relationship for relationship queries. For more information about Salesforce relationship queries, see the Salesforce SOQL Reference document.
  3. If you enter a query in the Editor without using code completion, be sure to use the actual/internal name for a table or column. The actual/internal name is the name used in the Object Explorer and Database Explorer.

    Note: Do not use a table's Display Name in the query. This is the user-friendly name provided to help you recognize the table.

  4. Click Run SQL. For more information about using the Editor, see About Editing SQL.

Note: Attempting to execute a SQL query (instead of a SOQL query) in the Editor against a Salesforce datasource can result in an error.

To create a cross-connection query using Salesforce connection

Toad uses ANSI SQL in a cross-connection query that includes a Salesforce connection.

  • If you are using the Cross-Connection SQL Editor to create a query against a Salesforce connection, use SQL (instead of SOQL).
  • If you are using the Cross-Connection Query Builder to build a query against a Salesforce connection, Toad generates the query in SQL.

Table and Column Names

Toad uses the actual name internal to Salesforce for tables (Salesforce objects) and columns (Salesforce fields). This actual/internal name is used in the Object Explorer and the Database Explorer.

In the Query Builder, Toad uses the actual/internal names for tables and columns in the generated query in the Query tab. You must also use the internal/actual name for a table or column when creating a SOQL query in the Editor.

Toad also displays a user-friendly name for each table and column. The user-friendly name is displayed in the Object Explorer in the Display Name column (for tables) and in the Comment column (for columns).

Considerations and Limitations

Consideration/Limitation Description
Parent-to-child relationships are not supported in Toad

In Salesforce relationship queries, Toad supports only child-to-parent relationships. Parent-to-child relationships are not supported. This limitation applies in the Editor and the Query Builder.

Tip: This means you can only join tables in the direction of child (foreign-key table) to parent.

For more information about Salesforce relationship queries, see the Salesforce SOQL reference document.

Important SOQL limitations

Please note the following limitations of Salesforce SOQL when creating a query in the Query Builder or Editor:

  • A SOQL query can include only one child table.

    Tip: This means only one table is allowed in the FROM clause.

  • A Salesforce SOQL query can have no more than two sub-queries.
Query Builder and new relationships

When building a Salesforce SOQL query, you cannot create new relationships using the Query Builder. Only the existing Salesforce relationships can be used.

Tip: To restore a dropped relationship between tables, right-click the child table and select Restore FK.

The Query Builder allows only one relationship per pair of tables

When building a Salesforce SOQL query, each pair of related tables in the diagram can have only one relationship.

Workaround: To add additional existing relationships, add multiple instances of a table.

Query Builder SOQL syntax limitations

The Query Builder does not support the following SOQL syntax:

  • TYPEOF typeOfField whenExpression elseExpression END

  • WITH DATA CATEGORY filteringExpression in a sub-query or Where clause
  • GROUP BY ROLLUP (fieldName1[, fieldName2, fieldName3])

  • GROUP BY CUBE (fieldName1[, fieldName2, fieldName3])

  • ? NULLS FIRST | LAST ? in an ORDER BY clause

  • FOR VIEW

  • FOR REFERENCE

  • OFFSET n

  • UPDATE VIEWSTAT

Workaround: Use the Editor to execute a query that includes any of the syntax listed above.

Query Builder does not support SOQL polymorphism

Toad does not support Salesforce SOQL polymorphism in the Query Builder.

Workaround: Use the Editor to execute a SOQL query that uses polymorphism.

Bind variables are not supported for Salesforce connections Avoid using bind variables with Salesforce
Query Builder functionality

Some functionality in the Query Builder is disabled for Salesforce connections, for example:

  • You cannot create calculated fields.
  • You cannot add a UNION operator.
Explain Plan is disabled Explain Plan is not available for a Salesforce SOQL query.
Cannot reverse engineer a SOQL query You cannot send a SOQL query from the Editor to the Query Builder to generate a diagram.
Cannot create a Toad View You cannot save a Salesforce SOQL query as a Toad View.
Data Compare is not available Data Compare is not available for Salesforce SOQL data at this time.
Commenting out is not available You cannot comment out a Where clause, Having clause, or column from a Select statement in a Salesforce SOQL query.

  

Related Topics

About Salesforce.com Data Sources

Related Documents