SOQL and SOSL
v Statement
Character Limit—By default, SOQL statements cannot exceed 20,000 characters in
length. For SOQL statements that exceed this maximum length, the API returns a MALFORMED_QUERY exception
code; no result rows are returned..
v Localized
Results—SELECT statements can include the toLabel() and convertCurrency() functions
in support of localized fields.
v Dynamic SOQL
in Apex—Apex requires that you surround SOQL and SOSL statements with
square brackets in order to use them on the fly. Additionally, Apex script
variables and expressions can be used if preceded by a colon (:).
v Ordered
Results—There is no guarantee of the order of results unless you use an ORDER
BY clause in a query.
Escaped
Character Examples
SELECT Id FROM Account WHERE Name LIKE 'pra%'
Select
all accounts whose name begins with the three character sequence 'pra'.
SELECT Id FROM Account WHERE Name LIKE 'pra\%'
Select
all accounts whose name exactly matches the four character sequence 'pra%'.
SELECT Id FROM Account WHERE Name LIKE 'pra\%%'
Select all
accounts whose name begins with the four character sequence 'pra%'
For example, to query the Account Name field for “prasad's
vara,” use the following SELECT statement:
SELECT
Id FROM Account WHERE Name LIKE 'prasad\'s vara'
SOQL SELECT Syntax
The SOQL SELECT statement uses the
following syntax:
SELECT fieldList
[TYPEOF typeOfField
whenExpression elseExpression END]
FROM objectType
[WHERE conditionExpression]
[WITH [DATA CATEGORY] filteringExpression]
[GROUP BY fieldGroupByList] | [GROUP BY ROLLUP|CUBE (fieldSubtotalGroupByList)]
[HAVING havingConditionExpression]
[ORDER BY fieldOrderByList
ASC |
DESC ? NULLS FIRST | LAST ?]
[LIMIT ?]
[OFFSET ?]
[FOR VIEW | FOR REFERENCE]
[UPDATE VIEWSTAT ?]
v TYPEOF
is currently available as a Developer
Preview as part of the SOQL Polymorphism feature. For more information on
enabling TYPEOF for
your organization, contact salesforce.com.
Condition Expression Syntax (WHERE Clause):
• SELECT Name FROM Account WHERE Name like
'A%'
• SELECT Id FROM Contact WHERE Name LIKE
'A%' AND MailingCity='California'
• SELECT Name FROM Account WHERE CreatedDate
> 2011-04-26T10:00:00-08:00
//You can use date or datetime values, or date literals. The format for
date and dateTime fields are different.
• SELECT
Amount FROM Opportunity WHERE CALENDAR_YEAR(CreatedDate) = 2011
null in SOQL Queries
SELECT
AccountId FROM Event WHERE ActivityDate != null
toLabel()
A
client application can have results from a query returned that are translated
into the user’s language, using toLabel():
It
is particularly useful for organizations that have the Translation Workbench
enabled.
You
can use toLabel() to
filter records using a translated picklist value.
example:
SELECT
Company, toLabel(Status) FROM Lead WHERE toLabel(Status) = 'le Draft'
Note: The to Label() method cannot be used with ORDER BY. Sales force always uses
the pick list’s defined order,
just
like reports. Also, you can’t use toLabel() in the WHERE clause
for division or currency ISO code picklists.
Querying
Multi-Select Picklists:
We
will use includes and excludes in multiselect picklist,
INCLUDES , EXCLUDES Applies
only to multi-select picklists.
SELECT
Id, rating FROM account WHERE rating =
‘cold;warm’
SELECT
Id, rating FROM account WHERE rating includes ( ‘cold,warm’)
IN
keyword in soql:
IN and NOT IN can
also be used for semi-joins and anti-joins when querying on ID (primary key) or
reference
(foreign
key) fields.
If
the value equals any one of the specified values in a WHERE
clause.
Ex:
SELECT
Name FROM Account WHERE BillingState IN ('California', 'New York')
NOT IN:
SELECT
Name FROM Account WHERE BillingState NOT IN ('California', 'New York')
ID field Semi-Join:
SELECT
Id, Name
FROM
Account
WHERE Id
IN
(
SELECT AccountId
FROM Opportunity
WHERE StageName = 'Closed Lost'
)
ID field Anti-Join
The
following query returns account IDs for all accounts that do not have any open
opportunities:
SELECT Id
FROM
Account
WHERE Id
NOT IN
(
SELECT
AccountId
FROM
Opportunity
WHERE
IsClosed = false
)
Date
Formats and Date Literals:
SELECT Id
FROM Account WHERE CreatedDate = YESTERDAY
SELECT Id
FROM Account WHERE CreatedDate < THIS_WEEK
SELECT Id
FROM Opportunity WHERE CloseDate = NEXT_MONTH
SELECT Id
FROM Account WHERE CreatedDate = LAST_90_DAYS
SELECT Id
FROM Account WHERE CreatedDate = LAST_N_DAYS:365
SELECT Id
FROM Account WHERE CreatedDate > LAST_QUARTER
SELECT Id
FROM Opportunity WHERE CloseDate = THIS_YEAR
ORDER
BY
You
can use ORDER BY in
a SELECT statement to control the order of the
query results. There is no guarantee of the order of results unless you use an ORDER
BY clause in a query.
Specifies
whether the results are ordered in ascending (ASC) or descending (DESC) order. Default order is ascending.
SELECT
Name FROM Account ORDER BY Name DESC NULLS LAST
• These data types are
not supported:
multi-select picklist, rich text area,
long text area, encrypted (if enabled), and data category group reference (if
Salesforce Knowledge is enabled).
LIMIT
Use
LIMIT to specify the maximum number of rows to
return:
SELECT
Name FROM Account limit 5
OFFSET
Eexample
:
if
a SOQL query normally returned 50 rows, you could use OFFSET
10 in your query to skip the first 10 rows:
• The maximum offset is
2,000 rows. Requesting an offset greater than 2,000 will result in a
NUMBER_OUTSIDE_VALID_RANGE error.
• OFFSET is intended to be used in a top-level
query, and is not allowed in most sub-queries.
SELECT Name FROM account LIMIT 50 OFFSET 10
UPDATE
VIEWSTAT:
The
UPDATE VIEWSTAT clause
is used in a SELECT statement
to report on Salesforce Knowledge article searches and views.It allows
developers to update an article’s view statistics.
GROUP
BY:
you can use GROUP BY with aggregate functions, such as SUM()
or MAX(), to summarize
the
data and enable you to roll up query results rather than having to process the
individual records in your code.
SELECT
LeadSource, COUNT(Name) FROM Lead GROUP BY LeadSource
SELECT
Name, Max(CreatedDate) FROM Account GROUP BY Name LIMIT 5
SELECT
Name n, MAX(Amount) max FROM Opportunity GROUP BY Name
GROUP
BY ROLLUP:
you can use GROUP BY ROLLUP to add subtotals for aggregated data in query
results. This allows the query to calculate subtotals so you don't have to
maintain that logic in your code. Use GROUP
BY ROLLUP with aggregate functions, such as SUM()
and COUNT(fieldName)..
SELECT
LeadSource, COUNT(Name) cnt FROM Lead GROUP BY ROLLUP(LeadSource)
SELECT
Status, LeadSource, COUNT(Name) cnt FROM Lead GROUP BY ROLLUP(Status,
LeadSource)
Using GROUPING(fieldName) to
Identify Subtotals
When you use GROUP
BY ROLLUP or GROUP BY CUBE to add subtotals to your query results, you can use the
GROUPING(fieldName) function to identify if a row is a subtotal for a
field.
SELECT
LeadSource, Rating, GROUPING(LeadSource) grpLS, GROUPING(Rating) grpRating,
COUNT(Name) cnt
FROM Lead
GROUP BY ROLLUP(LeadSource, Rating)
GROUP BY CUBE
you can use GROUP BY CUBE to add subtotals for every possible combination of
grouped field in the query results. This is particular useful if you need to
compile cross-tabular reports of your data. Use GROUP BY CUBE with aggregate functions, such as SUM()
and COUNT(fieldName).
SELECT
Type, BillingCountry,GROUPING(Type) grpType, GROUPING(BillingCountry)
grpCty,COUNT(id) accts FROM Account
GROUP BY
CUBE(Type, BillingCountry) ORDER BY GROUPING(Type), GROUPING(BillingCountry)
HAVING
you
can use a HAVING clause
with a GROUP BY clause
to filter the results returned by aggregate functions, such as SUM(). A HAVING clause
is similar to a WHERE clause.
The difference is that you can include aggregate functions in a HAVING
clause, but not in a WHERE
clause.
SELECT
LeadSource, COUNT(Name) FROM Lead GROUP BY LeadSource HAVING COUNT(Name) >
100
TYPEOF
Use
TYPEOF in SELECT clauses when querying data that contains polymorphic
relationships. A TYPEOF expression
specifies a set of fields to select that depend on the runtime type of the
polymorphic reference.
Syntax:
SELECT [fieldList,]
TYPEOF typeOfField
WHEN whenObjectType THEN
whenFieldList
[ELSE elseFieldList]
END
FROM objectType
SELECT
Name FROM Account
WHERE
CreatedById IN
(
SELECT
TYPEOF
Owner
WHEN User
THEN Id
WHEN
Group THEN CreatedById
END
FROM CASE
)
FOR
VIEW:
When
this clause is used with a query, two things happen:
• The
LastViewedDate field for
the retrieved record is updated.
• A
record is added to the RecentlyViewed object to reflect the recently viewed
data for the retrieved record.
SELECT
Name, ID FROM Contact LIMIT 1 FOR
VIEW
FOR
REFERENCE:
When
this clause is used with a query, two things happen:
• The
LastReferencedDate field
is updated for any retrieved records.
• A
record is added to the RecentlyViewed object to reflect the recently referenced
data for each retrieved record.
SELECT
Name, ID FROM Contact LIMIT 1 FOR
REFERENCE
Aggregate
Functions:
SELECT
AVG(Amount) FROM Opportunity
SELECT
CampaignId, AVG(Amount) FROM Opportunity GROUP BY CampaignId
SELECT
COUNT() FROM Account WHERE Name LIKE 'a%'
SELECT
COUNT_DISTINCT(Company) FROM Lead
SELECT
MIN(CreatedDate), FirstName, LastName FROM Contact GROUP BY FirstName, LastName
SELECT
Name, MAX(BudgetedCost) FROM Campaign GROUP BY Name
SELECT
SUM(Amount) FROM Opportunity WHERE IsClosed = false AND Probability > 60
Querying
Currency Fields in Multicurrency Organizations:
SELECT
Id, convertCurrency(AnnualRevenue) FROM Account
SELECT
Name, MAX(Amount) FROM Opportunity GROUP BY Name HAVING MAX(Amount) > 10000
Relationship
Queries:
Child
to parent:
SELECT
Contact.FirstName, Contact.Account.Name from Contact
Parent to child
SELECT
Account.Name, (SELECT Contact.FirstName, Contact.LastName FROM
Account.Contacts) FROM Account
No comments:
Post a Comment