Friday, 16 August 2013

SOQL

SOQL Queries:

SOQL can be used in the following areas:
  1. In the queryString parameter  in  the query call.
  2. In the Apex Statements.
  3. In the Visualforce controllers and getter methods.
  4. In the Schema Explorer of the Force.com IDE.

Syntax of SOQL:
 
SELECT one or more fields
FROM  an object
WHERE filter statements and, optionally, results are ordered.

Example: SELECT id,name from Account where name='Naveen'


Using Alias notation in SOQL:
Select id,name from Contact c,c.Account a where a.name='Wipro'
  • The above query returns the contacts whose account name is 'Wipro'.
  • SOQL keywords can't be used as alias names.

SOQL Keywords:
  1. AND
  2. ASC
  3. DESC
  4. EXCLUDES
  5. FIRST
  6. FROM
  7. GROUP
  8. HAVING
  9. IN 
  10. INCLUDES
  11. LAST 
  12. LIKE 
  13. LIMIT
  14. NOT 
  15. NULL
  16. NULLS
  17. OR
  18. SELECT 
  19. WHERE 
  20. WITH
Reserved Characters of SOQL:
'(single quote) and \(back slash) are reserved characters in SOQL

For Example to Query the Account name field for "Naveen's Enclave", use the following syntax.

SELECT Id,Name
FROM Account 
WHERE Name Like 'Naveen\'s Enclave'

Implementation Tips:
  • By Default SOQL Statements can't exceed 20k characters. For SOQL Statements Exceed this length, the API returns MALFORMED_QUERY Exception code; no result rows are returned.
  • Select Statements can include the toLabel() and convertCurrency() functions in support of localization fields.
  • Apex Script variables and expressions can be used if preceded by a column(:).
  • There is no guarantee of the order of results unless you use an ORDER BY clause in a query.