Salesforce Object Query Language ( SOQL )
Use the Salesforce Object Query Language (SOQL) to search your organization’s Salesforce data for specific information. SOQL is similar to the SELECT statement in the widely used Structured Query Language (SQL) but is designed specifically for Salesforce data.
Salesforce Object Query Language (SOQL)은 사용하는 Org.의 데이터를 조회합니다.
SQL (Structured Query Language)의 SELECT 문장과 비슷하지만 Salesforce 전용으로 설계되어 있습니다.
SOQL를 사용하면 아래 환경에서 단순하면서 강력한 조회구문을 생성 할 수 있습니다.
- In the queryString parameter in the query() call
- In Apex statements
- In Visualforce controllers and getter methods
- In the Schema Explorer of the Force.com IDE
SOQL는 Join 구문, wildcards('scal%','_ing'), calculation expressions 지원하지 않습니다.
SOQL에서는 SELECT STATEMENT를 압축하여 steitment와 조합하여 사용하고 필요에 따라 정렬할 수 있는 변경 가능
SELECT one or more fields FROM an object WHERE filter statements and, optionally, results are ordered |
예를 들어, 다음 SOQL 쿼리는 Name 값이 Sandy인 모든 거래처 레코드의 Id 및 Name 항목 값을 반환합니다.
SELECT Id , Name FROM Account WHERE Name = ' Sandy ' |
메모
Apex에서는 SOQL 스테이트먼트나 SOSL 스테이트먼트를 그 자리에서 사용하려면 각괄호로 둘러싸야 합니다.
앞에 콜론(:)이 있는 경우는 Apex 스크립트 변수와 식을 사용할 수 있습니다.
구문에 대한 자세한 내용은 "SOQL SELECT 구문"을 참조하십시오.
SOQL을 사용하는 케이스
데이터가 어떤 오브젝트에 존재하고 있는지를 인식하고 있으며 다음 조작을 할 경우에는 SOQL을 사용합니다.
1개의 오브젝트 또는 서로 관련된 복수의 오브젝트로부터 데이터를 취득한다.
지정된 조건을 만족하는 레코드의 수를 카운트한다.
질의 일부로서 결과를 정렬한다.
수치, 날짜 또는 체크 박스 항목에서 데이터를 가져온다.
메모
아카이브 데이터와 Big Object에서는 일부 SOQL 기능만 사용할 수 있습니다.자세한 내용은 "Big Object를 사용하는 SOQL"을 참조하십시오.
SOQL SELECT Syntax
SOQL query syntax consists of a required SELECT statement followed by one or more optional clauses, such as TYPEOF, WHERE, WITH, GROUP BY, and ORDER BY.
The SOQL SELECT statement uses the following syntax:
SELECT fieldList [subquery][...] [TYPEOF typeOfField whenExpression[...] elseExpression END][...] FROM objectType[,...] [USING SCOPE filterScope] [WHERE conditionExpression] [WITH [DATA CATEGORY] filteringExpression] [GROUP BY {fieldGroupByList|ROLLUP (fieldSubtotalGroupByList)|CUBE (fieldSubtotalGroupByList)} [HAVING havingConditionExpression] ] [ORDER BY fieldOrderByList {ASC|DESC} [NULLS {FIRST|LAST}] ] [LIMIT numberOfRowsToReturn] [OFFSET numberOfRowsToSkip] [FOR {VIEW | REFERENCE}[,...] ] [ UPDATE {TRACKING|VIEWSTAT}[,...] ]
Note
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.
SyntaxDescription
fieldList subquery | Specifies a list of one or more fields, separated by commas, that you want to retrieve from the specified object. The bold elements in the following examples are fieldlists:
The fieldlist can also be an aggregate function, such as COUNT() and COUNT(fieldName), or be wrapped in Translating Results. |
typeOfField | A polymorphic relationship field in objectType or a polymorphic field in a parent of objectType that can reference multiple object types. For example, the What relationship field of an Event could be an Account, a Campaign, or an Opportunity. typeOfField cannot reference a relationship field that is also referenced in fieldList. See TYPEOF for more information. |
whenExpression | A clause of the form WHEN whenObjectType THEN whenFieldList. You can have one or more whenExpression clauses inside a TYPEOF expression. See TYPEOF for more information. |
elseExpression | A clause of the form ELSE elseFieldList. This clause is optional inside a TYPEOF expression. See TYPEOF for more information. |
objectType | Specifies the type of object that you want to query(). You must specify a valid object, such as Account, and must have read-level permissions to that object. |
filterScope | Available in API version 32.0 and later. Specifies the filterScope for limiting the results of the query. |
conditionExpression | If WHERE is specified, determines which rows and values in the specified object (objectType) to filter against. If unspecified, the query() retrieves all the rows in the object that are visible to the user. |
filteringExpression | If WITH DATA CATEGORY is specified, the query() only returns matching records that are associated with the specified data categories and are visible to the user. If unspecified, the query() returns the matching records that are visible to the user. The WITH DATA CATEGORY clause only filters objects of type:
|
fieldGroupByList | Available in API version 18.0 and later. Specifies a list of one or more fields, separated by commas, that are used to group the query results. A GROUP BY clause is used with aggregate functions to summarize the data and enable you to roll up query results rather than having to process the individual records in your code. See GROUP BY. |
fieldSubtotalGroupByList | Available in API version 18.0 and later. Specifies a list of up to three fields, separated by commas, that are used to group the query results. The results include extra subtotal rows for the grouped data. See GROUP BY ROLLUP and GROUP BY CUBE. |
havingConditionExpression | Available in API version 18.0 and later. If the query includes a GROUP BY clause, this conditional expression filters the records that the GROUP BY returns. See HAVING. |
fieldOrderByList | Specifies a list of one or more fields, separated by commas, that are used to order the query results. For example, you can query for contacts and order the results by last name, and then by first name: |
Note the following implementation tips:
- 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.
- Note
Long, complex SOQL statements, such as statements that contain many formula fields, can sometimes result in a QUERY_TOO_COMPLICATED error. The error occurs because the statement is expanded internally when processed by Salesforce, even though the original SOQL statement is under the 20,000 character limit. To avoid this, reduce the complexity of your SOQL statement.
- Localized Results—SELECT statements can include the Translating Results, convertCurrency(), and FORMAT() functions in support of localized fields.
- Dynamic SOQL in Apex—Apex requires that you surround SOQL and SOSL statements with square brackets to use them on the fly. You can use Apex script variables and expressions when preceded by a colon (:).
- Ordered Results—The order of results is not guaranteed unless you use an ORDER BY clause in a query.
- Picklist Values—In API version 39.0 and later, query for picklist values by the value’s API name, which may differ from the actual value.
'개발자정보' 카테고리의 다른 글
npm 설치 (Node.js 설치) (0) | 2021.09.21 |
---|---|
세일즈포스(Salesfroce) SOQL 예제 총 정리 (0) | 2021.09.20 |
Apex 트리거 (0) | 2021.09.20 |
세일즈포스 인터뷰 질문 2015 (0) | 2021.09.19 |
세일즈포스(Salesforce) Trailhead Playground Management (0) | 2021.09.19 |