SELECT Statements

TIBCO Data Virtualization® Adapter Online Help > TDV Salesforce with SSO Adapter > SQL Compliance > SELECT Statements

SELECT Statements

A SELECT statement can consist of the following basic clauses.

SELECT

INTO

FROM

JOIN

WHERE

GROUP BY

HAVING

UNION

ORDER BY

LIMIT

SELECT Syntax

The following syntax diagram outlines the syntax supported by the Salesforce with SSO adapter:

SELECT {

[ TOP <numeric_literal> ]

{

| {

<expression> [ [ AS ] <column_reference> ]

| { <table_name> | <correlation_name> } .*

} [ , ... ]

}

[ INTO csv:// [ filename= ] <file_path> [ ;delimiter=tab ] ]

{

FROM <table_reference> [ [ AS ] <identifier> ]

} [ , ... ]

[ [

INNER | { { LEFT | RIGHT | FULL } [ OUTER ] }

] JOIN <table_reference> [ ON <search_condition> ] [ [ AS ] <identifier> ]

] [ ... ]

[ WHERE <search_condition> ]

[ GROUP BY <column_reference> [ , ... ]

[ HAVING <search_condition> ]

[

ORDER BY

{ <column_reference> [ ASC | DESC ] } [ , ... ]

]

[

LIMIT <expression>

]

} | SCOPE_IDENTITY()

<expression> ::=

| <column_reference>

| @ <parameter>

| ?

| COUNT( * | { <expression> } )

| { AVG | MAX | MIN | SUM | COUNT } ( <expression> )

| <literal>

| <sql_function>

<search_condition> ::=

{

<expression> { = | > | < | >= | <= | <> | != | LIKE | NOT LIKE | IS NULL | IS NOT NULL | IN | NOT IN | AND | OR } [ <expression> ]

} [ { AND | OR } ... ]

Examples

Return all columns:

SELECT * FROM Account

Rename a column:

SELECT "Name" AS MY_Name FROM Account

Search data:

SELECT * FROM Account WHERE Industry = 'Floppy Disks';

The Salesforce with SSO APIs support the following operators in the WHERE clause: =, >, <, >=, <=, <>, !=, LIKE, NOT LIKE, IS NULL, IS NOT NULL, IN, NOT IN, AND, OR.

SELECT * FROM Account WHERE Industry = 'Floppy Disks';

Return the number of items matching the query criteria:

SELECT COUNT(*) AS MyCount FROM Account

Return the number of unique items matching the query criteria:

SELECT COUNT(DISTINCT Name) FROM Account

Summarize data:

SELECT Name, MAX(AnnualRevenue) FROM Account GROUP BY Name

See Aggregate Functions for details.

Retrieve data from multiple tables.

SELECT Account.Id, Account.Name, Account.Fax, Opportunity.AccountId, Opportunity.CloseDate FROM Account INNER JOIN Opportunity ON Account.Id = Opportunity.AccountId

See JOIN Queries for details.

Sort a result set in ascending order:

SELECT BillingState, Name FROM Account ORDER BY Name ASC

Aggregate Functions

Examples of Aggregate Functions

Below are several examples of SQL aggregate functions. You can use these with a GROUP BY clause to aggregate rows based on the specified GROUP BY criterion. This can be a reporting tool.

COUNT

Returns the number of rows matching the query criteria.

SELECT COUNT(*) FROM Account WHERE Industry = 'Floppy Disks'

COUNT_DISTINCT

Returns the number of distinct, non-null field values matching the query criteria.

SELECT COUNT_DISTINCT(BillingState) AS DistinctValues FROM Account WHERE Industry = 'Floppy Disks'

AVG

Returns the average of the column values.

SELECT Name, AVG(AnnualRevenue) FROM Account WHERE Industry = 'Floppy Disks' GROUP BY Name

MIN

Returns the minimum column value.

SELECT MIN(AnnualRevenue), Name FROM Account WHERE Industry = 'Floppy Disks' GROUP BY Name

MAX

Returns the maximum column value.

SELECT Name, MAX(AnnualRevenue) FROM Account WHERE Industry = 'Floppy Disks' GROUP BY Name

SUM

Returns the total sum of the column values.

SELECT SUM(AnnualRevenue) FROM Account WHERE Industry = 'Floppy Disks'

JOIN Queries

This section provides information about the features and restrictions that are specific to how Salesforce supports joins. The Salesforce with SSO Adapter supports join queries based on SOQL (Salesforce Object Query Language). The adapter supports standard SQL syntax instead of proprietary SOQL to allow easy integration with a wide variety of SQL tools. Join queries in Salesforce are based on the relationships among Salesforce objects.

Relationship Queries

Salesforce objects can be linked using relationships. The standard Salesforce objects have predefined relationships. You can define relationships for your custom objects.

Parent to Child Relationships

Salesforce relationships are directional and are of the following types: one-to-many (parent to child) or many-to-one (child to parent). Since the relationships are directional the order in which the tables are included in the query determines the path of relationship traversal.

The following query shows a simple parent-to-child join query. This query returns all Accounts and the first and last name of each Contact associated with that Account.

SELECT Contact.FirstName, Account.Name

FROM Account, Contact

Polymorphic Relationships

Salesforce relationships can be polymorphic. That is, a given relationship on a field can refer to more than one type of entity. For example, the Task entity contains a Who relationship, which by default may refer to a Contact or Lead.

The following query shows a join based on a polymorphic relationship. To return only Tasks referring to a Contact on the Who relationship, specify the type of entity in the relationship:

SELECT Task.Subject, Contact.Name

FROM Task, Contact

WHERE Contact.Type='Contact'

Custom Relationships

You can specify a join condition that is a custom relationship. The following query retrieves the names of all Account records and the first names of all Contacts that match the specified join condition:

Select Contact.Firstname, Account.Name

FROM Account

JOIN Contact

ON Account.MyCustomColumn__c = Contact.Id

Supported Join Syntax

You can use the syntax detailed below to execute joins on Salesforce objects. You can only join related objects.

A benefit of predefined relationships is that you do not need to specify the join conditions to execute a join with Salesforce data; the conditions are already accounted for based on the relationship. The following query returns the first names of all the Contacts in the organization and for each Contact the name of the parent Account associated with that Contact.

Select Contact.Firstname, Account.Name

FROM Contact, Account

If there are multiple relationships between the tables, you can explicitly set the join criteria. The following query matches on a custom relationship determined by the columns in the join criteria (Account.MyCustomColumn__c and Contact.Id), instead of the default parent-to-child relationship between Accounts and Contacts:

Select Contact.Firstname, Account.Name

FROM Account

JOIN Contact

ON Account.MyCustomColumn__c = Contact.Id

Inner joins are supported. The following query retrieves all Account records that are associated with an Opportunity:

SELECT Account.Id, Account.Name, Account.Fax, Opportunity.AccountId, Opportunity.CloseDate

FROM Account

INNER JOIN Opportunity

ON Account.Id = Opportunity.AccountId

The following query shows a join between three custom objects. This query joins the custom tables NW_Product__c, NW_Category__c, and NW_Suppliers__c and returns the Name field from each of them. Additionally, the results from the NW_Category__c table are filtered for names that start with "Dairy":

SELECT A.Name, B.Name, C.Name

FROM NW_Product__c as A, NW_Category__c as B, NW_Suppliers__c as C

WHERE B.Name LIKE 'Dairy%'

Projection Functions

CONVERTCURRENCY(column)

Returns the currency field converted to the user's currency