Saturday, November 2, 2013

Salesforce + SOQL Query

Salesforce SOQL Query

===============
Basic Examples
===============

1) Simple Query
SELECT Name FROM Account WHERE Name like 'Test%' AND Status__c = 'Approved' LIMIT 1000

2) Query Filter on DateTime field
SELECT Name FROM Account WHERE CreatedDate > 2011-04-26T10:00:00-08:00
SELECT Name FROM Account WHERE CreatedDate > 2011-04-26T10:00:00Z

3) Query with Date Function("http://docs.database.com/dbcom/en-us/db_sosl_soql/sforce_api_calls_soql_select_date_functions.htm?version=186.0")
SELECT Amount FROM Opportunity WHERE CALENDAR_YEAR(CreatedDate) = 2011

4) Query filter on null
SELECT AccountId FROM Event WHERE ActivityDate != null

5) Query Multi-Select Picklists

The following operators are supported for querying multi-select picklists:

Operator Description
= Equals the specified string.
!= Does not equal the specified string.
includes Includes (contains) the specified string.
excludes Excludes (does not contain) the specified string.
; Specifies AND for two or more strings.

SELECT Id, MSP1__c from Account WHERE MSP1__c includes ('AAA;BBB','CCC')
this will return record with MSP__1 example: 'AAA;BBB;DDD' ; 'CCC;EEE'

6) Querying Currency Fields in Multi-currency Organizations
SELECT Id, Name FROM Opportunity WHERE Amount > JPY5000
without currency code it will use organization's default currency

===========================
Escaped Character Examples
===========================

1)SELECT Id FROM Widget__C WHERE Name LIKE R%'
Select all widgets whose name begins with the letter 'R'.

2) SELECT Id FROM Widget__c WHERE Name LIKE 'R\%'
Select all widgets whose name exactly matches the two character sequence 'R%'.

3)SELECT Id FROM Widget__c WHERE Name LIKE 'R\%%'
Select all widgets whose name begins with the two character sequence 'R%'

==============================
Reserved characters Examples
==============================

Reserved characters, if specified in a SELECT clause as a literal string (between single quotes), must be escaped (preceded by the backslash \ character) in
order to be properly interpreted. An error occurs if you do not precede reserved characters with a backslash.

The following characters are reserved:
' (single quote)
\ (backslash)

For example, to query the Widget__c Name field for “Wally’s grommet,” use the following SELECT statement:

SELECT Id FROM Widget__c WHERE Name LIKE 'Wally\'s grommet'

==========
Order By
==========

SELECT Name FROM Widget__c ORDER BY Name DESC NULLS LAST

The following limitations apply to data types when using ORDER BY:

1) These data types are not supported: multi-select picklist, rich text area, long text area, and encrypted (if enabled).
2)All other data types are supported, with the following caveats:
a)convertCurrency() always sorts using corporate currency value, if available.
b)phone data does not include any special formatting when sorting, for example, non-numeric characters such as dash or parentheses are included in the sorting.
c)picklist sorting is defined by the picklist sort determined during setup.
d)You are limited to 32 fields in an ORDER BY query. If you exceed the limit, a malformed query fault is returned.

============================
Where Conditional Expression
=============================
You can use parentheses to define the order in which fieldExpressions are evaluated.
For example, the following expression is true

if fieldExpression1 is true and either fieldExpression2 or fieldExpression3 are true:
fieldExpression1 AND (fieldExpression2 OR fieldExpression3)

However, the following expression is true if either fieldExpression3 is true or both fieldExpression1 and fieldExpression2 are true.
(fieldExpression1 AND fieldExpression2) OR fieldExpression3

===========
Join
===========

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

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

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

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

5) 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 )

===========
Set Based
===========

Select ID FROM Account Where ID IN : setAccountIds

===============
Relationship
===============

1) Parent to Child
SELECT Id, (SELECT Id from OpportunityLineItems) FROM Opportunity (Standard Version)
Select ID, (Select ID FROM Orders__r) FROM Account (Custom Object Version)

2) Child to Parent
SELECT Id, Name, Account.Name FROM Contact
Select ID, VCR__r.Name FROM Account

In a WHERE clause that checks for a value in a parent field, if the parent does not exist, the record is returned in version 13.0 and later,
but not returned in versions before 13.0.. For example:

SELECT Id FROM Model__c WHERE Widget__r.name = null

Model record Id values are returned in version 13.0 and later, but are not returned in versions before 13.0.

When designing relationship queries, consider these limitations:

1)Relationship queries are not the same as SQL joins. You must have a relationship between objects to create a join in SOQL.
2)No more than 35 child-to-parent relationships can be specified in a query. A custom object allows up to 25 relationships, so you can reference all the child-to-parent relationships for a custom object in one query.
3)No more than 20 parent-to-child relationships can be specified in a query.
4)In each specified relationship, no more than five levels can be specified in a child-to-parent relationship.
For example, Contact.Account.Owner.FirstName (three levels).
5)In each specified relationship, only one level of parent-to-child relationship can be specified in a query.
For example, if the FROM clause specifies Account, the SELECT clause can only specify the Contact or other objects at that level.
It could not specify a child object of Contact.

3) Polymorphic
A polymorphic relationship field in object being queried that can reference multiple object types.
For example, the What relationship field of an Event could be an Account, or a Campaign, or an Opportunity.

SELECT Id FROM Event WHERE What.TYPE IN ('Account', 'Opportunity')

======================
Query History Object
=======================

Custom objects and some standard objects have an associated history object that tracks changes to an object record.
You can use relationship queries to traverse a history object to its parent object.
For example, the following query returns every history row for Foo__c and displays the name and custom fields of Foo:

SELECT OldValue, NewValue, Parent.Id, Parent.name, Parent.customfield__c FROM foo__history
This example query returns every Foo object row together with the corresponding history rows in nested subqueries:

SELECT Name, customfield__c, (SELECT OldValue, NewValue FROM foo__history) FROM foo__c

============
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

Limitations with OFFSET
========================
1)The maximum offset is 2,000 rows. Requesting an offset greater than 2,000 will result in a NUMBER_OUTSIDE_VALID_RANGE error.
2)OFFSET is intended to be used in a top-level query, and is not allowed in most sub-queries, so the following query is invalid and
will return a MALFORMED_QUERY error.
3)A sub-query can use OFFSET only if the parent query has a LIMIT 1 clause. The following query is a valid use of OFFSET in a sub-query.

==================
Aggregate Queries
===================
From API version 18.0 and later, you can use GROUP BY with aggregate functions, such as COUNT(), SUM() or MAX().

a) With GROUP BY

SELECT Stagename, COUNT(Id) FROM Opportunity GROUP BY Stagename
SELECT LeadSource, COUNT(Name) FROM Lead GROUP BY LeadSource
SELECT Stagename, SUM(amount) FROM Opportunity GROUP BY Stagename
SELECT CALENDAR_YEAR(CloseDate), COUNT(Id) FROM Opportunity GROUP BY CALENDAR_YEAR(CloseDate) ORDER BY CALENDAR_YEAR(CloseDate)


b) 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)

c) With GROUP BY ROLLUP with 2 fields
SELECT Status, LeadSource, COUNTId) FROM Lead GROUP BY ROLLUP(Status, LeadSource)

d) 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

SELECT Widget__c, COUNT(Name) FROM Model__c GROUP BY Widget__c HAVING COUNT(Name) > 10 and Model_Number__c LIKE 'Sirius%'

===========================
Geolocation SOQL Queries
===========================

Following are some sample geolocation SOQL queries:

SELECT warehouse_location__latitude__s, warehouse_location__longitude__s FROM Warehouse__c
This query finds all of the warehouses that are stored in the custom object Warehouse. The list will display each warehouse’s latitude and longitude values.
Note the use of the separate field components for warehouse_location__c, to select the latitude and longitude components individually.


SELECT name__c, address__c FROM Warehouse__c WHERE DISTANCE(warehouse_location__c, GEOLOCATION(37.775,-122.418), “mi”) < 20
ORDER BY DISTANCE(warehouse_location__c, GEOLOCATION(37.775,-122.418), “mi”)

This query finds all of the warehouses in the custom object Warehouse that are within 20 miles of the geolocation 37.775°, –122.418°,
which is San Francisco. The list will show the name and address of each warehouse, but not its geocoordinates.
The nearest warehouse will be first in the list; the farthest location will be last.
Note that, when used in the DISTANCE function, you use the compound geolocation field warehouse_location__c directly.