Thursday, December 25, 2008

Salesforce Object Query Language (SOQL)

SELECT fieldList FROM objectType
[WHERE conditionExpression]
[ORDER BY fieldName ASC | DESC ? NULLS FIRST | LAST ?]
LIMIT ?

Examples:


1. SELECT Id, Name, BillingCity FROM Account
2. SELECT count() FROM Contact
3. SELECT Contact.Firstname, Contact.Account.Name FROM Contact
4. SELECT Account.Name, (SELECT Contact.LastName FROM Account.Contacts)
FROM Account
5. SELECT Name FROM Account WHERE Name like 'A%'
6. SELECT Id FROM Contact WHERE Name like 'A%' AND
MailingCity='California'

7. SELECT Id FROM Case WHERE Contact.Lastname = null
8. SELECT AccountId, FirstName, lastname FROM Contact WHERE lastname LIKE 'appl_%'
9. SELECT Name FROM ACCOUNT WHERE BillingState IN ('California', 'New York')
10. SELECT Name FROM ACCOUNT WHERE BillingState NOT IN ('California', 'New York')
11. SELECT Id, Name FROM Account
WHERE Id IN (SELECT AccountId FROM Opportunity WHERE StageName = 'Closed Lost')
12. SELECT Id
FROM Account
WHERE Id NOT IN (SELECT AccountId FROM Opportunity WHERE IsClosed = false)
13. 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)
14. SELECT Id, (SELECT Id from OpportunityLineItems)
FROM Opportunity
WHERE Id IN
(SELECT OpportunityId FROM OpportunityLineItem WHERE totalPrice > 10000)
15. etc.

Escape sequence:
Sequence Meaning
\n New line
\r Carriage return
\t Tab
\b Bell
\f Form feed
\" One double-quote character
\' One single-quote character
\\ Backslash

Example with escape sequence:
SELECT Id FROM Account WHERE Name LIKE 'Bob\'s BBQ'
More information about query with SOQL, read here

Note that NULL operator is: '= null' or '!= null', NOT 'IS NULL' or 'IS NOT NULL'

Source: salesforce.com

3 comments:

  1. Can anybody give me an example of how to use '\n' escape sequence in SOQL as "SELECT Id FROM Account WHERE Name LIKE 'Bob\'s BBQ'" which is given above.

    Any help is appreciated.

    ReplyDelete
  2. The owner decided after the event to donate half of the proceeds back to
    the students. You make different challenges like scavenger hunts,
    food challenges, mazes and races. Spice things up by including these
    Halloween-themes recipes:.

    my website ... liposuction

    ReplyDelete
  3. Choose from the list of the best kayaks available and get value for your money.
    Are you looking for a stable casting platform or a sleek,
    fast-moving boat to chase blitzing fish. Now let us look at
    the specific needs of a kayak fishermen when it comes to
    his craft.

    Here is my weblog ... Baltimore Maryland

    ReplyDelete