Pages

Thursday, September 12, 2013

Salesforce: SOQL (Salesforce Object Query Language) Reference

Using the a query tool of your choice (e.g., the Developer Console Query Editor, Workbench), you can execute SOQL query to get raw data from Salesforce, where some of this not available from report.

Simple query
SELECT Id, Name FROM Account WHERE Name LIKE 'A%'
SELECT Id, Name FROM Account WHERE Name LIKE 'A%' AND BillingCity = 'Redwood City'
SELECT Id, Name FROM Account WHERE CurrencyIsoCode = 'USD' OR CurrencyIsoCode = 'SGD'

Query with LIKE and NOT
SELECT a.Id, a.Name FROM Account a WHERE NOT a.Name LIKE 'A%'
** NOT should be put before field name
** Notice usage of a variable

Query filter on DateTime
SELECT Name FROM Account WHERE CreatedDate >= 2011-04-26T10:00:00-08:00
SELECT Name, CreatedDate FROM Account WHERE CreatedDate > 2011-04-26T10:00:00Z

Query with Date function
SELECT Amount FROM Opportunity WHERE CALENDAR_YEAR(CreatedDate) = 2011

Query filter on Null value
SELECT Id, AccountId FROM Event WHERE ActivityDate != Null
SELECT Id, AccountId FROM Event WHERE ActivityDate = Null

Query Multi-Select Picklists
SELECT Id, Country__c from Account WHERE Country__c INCLUDES ('Indonesia;Singapore','Malaysia')

Semi-Join Query
SELECT Id, Name FROM Account WHERE Id IN ( SELECT AccountId FROM Opportunity WHERE StageName = 'Closed Lost' )

Reference Field Semi-Join Query
SELECT Id FROM Task WHERE WhoId IN ( SELECT Id FROM Contact WHERE MailingCity = 'Twin Falls' )

Anti-Join Query
SELECT Id FROM Account WHERE Id NOT IN ( SELECT AccountId FROM Opportunity WHERE IsClosed = False )

Reference Field Anti-Join Query
SELECT Id FROM Opportunity WHERE AccountId NOT IN ( SELECT AccountId FROM Contact WHERE LeadSource = 'Web' )

Multiple Semi-Joins Query
SELECT Id, Name FROM Account WHERE 
Id IN ( SELECT AccountId FROM Contact WHERE LastName LIKE 'apple%'AND 
Id IN ( SELECT AccountId FROM Opportunity WHERE isClosed = False )

Relationship Query: parent to child
SELECT Id, (SELECT Id,Name from OpportunityLineItems) FROM Opportunity

Relationship Query: child to parent 
SELECT Id, Name, Account.Name FROM Contact

Relationship Query: Polymorphic 
A polymorphic relationship field in object being queried that can reference multiple object types. For example, the What relationship field of an Event or Task could be an Account, or a Campaign, or an Opportunity.
SELECT Id FROM Task WHERE What.TYPE IN ('Account', 'Opportunity')

With OFFSET
Use OFFSET to specify the starting row offset into the result set returned by your query.
SELECT Id, Name FROM Opportunity ORDER BY Name OFFSET 5

With COUNT
Returns the number of rows matching the query criteria
SELECT COUNT() FROM Account WHERE Name LIKE 'a%'
SELECT COUNT(Id) FROM Account WHERE Name LIKE 'a%'
If you are using a GROUP BY clause, use COUNT(fieldName) instead of COUNT()

With COUNT_DISTINCT
Returns the number of distinct non-null field values matching the query criteria
SELECT COUNT_DISTINCT(Company) FROM Lead
This will return number of distinct value from Company field

With GROUP BY
From API version 18.0 and later, you can use GROUP BY with aggregate functions, such as COUNT(), SUM(), MAX(), MIN(), AVG()
SELECT Stagename, COUNT(Id) FROM Opportunity GROUP BY Stagename

SELECT LeadSource, COUNT(Name) FROM Lead GROUP BY LeadSource

SELECT StageName, SUM(amount), MAX(amount), MIN(amount), AVG(amount) FROM Opportunity GROUP BY StageName

SELECT CALENDAR_YEAR(CloseDate), COUNT(Id) FROM Opportunity GROUP BY CALENDAR_YEAR(CloseDate) ORDER BY CALENDAR_YEAR(CloseDate)

Using Alias with GROUP BY
You can use an alias for any field or aggregated field in a SELECT statement in a SOQL query. Use a field alias to identify the field when you’re processing the query results in your code.
SELECT StageName, SUM(amount) Jumlah, MAX(amount), MIN(amount), AVG(amount) FROM Opportunity GROUP BY StageName

With GROUP BY ROLLUP
Same with GROUP BY, with additional ROLLUP, it add subtotal for aggregated data in the last row
SELECT StageName, COUNT(Id) FROM Opportunity GROUP BY ROLLUP(StageName)


With GROUP BY ROLLUP with 2 fields
SELECT Status, LeadSource, COUNT(Id) FROM Lead GROUP BY ROLLUP(Status, LeadSource)

HAVING in GROUP BY
You can use a HAVING clause with a GROUP BY clause to filter the results returned by aggregate functions, same with WHERE with normal query
SELECT LeadSource, COUNT(Id) FROM Lead GROUP BY LeadSource HAVING COUNT(Id) > 2

Querying Currency Fields in Multi-currency Organizations
SELECT Id, Name, Amount, CurrencyIsoCode FROM Opportunity WHERE Amount > SGD5000 ORDER BY Amount
** without currency code it will use organization's default currency
** Amount in query result will be in record currency



ReferenceSalesforce Object Query Language (SOQL)


Last update: 24 April 2016