引自MSDN:http://msdn.microsoft.com/en-us/library/aa656402.aspx
SelectStatement | = |
|
Parameters |
|
|
FindOptions | = |
|
FieldList | = | Field |
Field | = | Aggregate |
Aggregate | = |
|
Options | = |
|
IndexClause | = |
|
WhereClause | = |
|
JoinClause | = | [ |
Keyword | Description | Example |
---|---|---|
asc | An option on the |
|
avg | Returns the average of the fields. |
|
count | Returns the number of records. |
|
crossCompany | Returns data for all companies that the user is authorized to read from. (A |
|
desc | An option on the |
|
exists | A method that returns a Boolean value and a |
|
firstFast | A priority hint. The first row appears more quickly but the total return time for this option might be slower. The |
|
firstOnly | Speeds up the fetch. Instructs MorphX to fetch only the first record. |
|
firstOnly10 | Same as firstOnly, except returns 10 rows instead of one. |
|
firstOnly100 | Same as firstOnly, except returns 100 rows instead of one. |
|
firstOnly1000 | Same as firstOnly, except returns 1000 rows instead of one. |
|
forceLiterals |
Note
You are advised not to use the forceLiterals keyword in X++
select statements, because it could expose code to an SQL injection security threat. forceLiterals instructs the kernel to reveal the actual values that are used in forceLiterals and forcePlaceholders are mutually exclusive. |
|
forceNestedLoop | Forces the Microsoft SQL Server database to use a nested-loop algorithm to process a particular SQL statement containing a join algorithm. This means that a record from the first table is fetched before any records from the second table are fetched. Typically, other join algorithms, such as hash-joins and merge-joins, would be considered. This keyword is often combined with the forceSelectOrder keyword. |
|
forcePlaceholders | Instructs the kernel not to reveal the actual values used in The advantage of using this keyword is that the kernel can reuse the access plan for other similar statements with other search values. The disadvantage is that the access plan is computed without taking into consideration that data distribution might not be even. The access plan is an on-average access plan. forcePlaceholders and forceLiterals are mutually exclusive. |
|
forceSelectOrder | Forces the SQL Server database to access the tables in a join in the specified order. If two tables are joined, the first table in the statement is always accessed first. This keyword is often combined with the forceNestedLoop keyword. |
|
forUpdate | Selects records exclusively for update. Depending on the underlying database, the records may be locked for other users. |
|
group by | Instructs the database to group selected records by fields. |
|
index | Instructs the database to sort the selected records as defined by the index. |
|
index hint | Gives the database a hint to use this index to sort the selected records as defined by the index. The database can ignore the hint.
Note
A wrong index hint can have a big performance impact. Index hints should only be applied to SQL statements that do not have dynamic
where clauses or order by clauses, and where the effect of the hint can be verified. |
|
join | Used to join tables on a column that is common to both tables. Reduces the number of SQL statements that are needed if you want to loop through a table and update transactions in a related table. For example, if you process 500 records in a table, and want to update related records in another table, and use a nested |
|
maxof | Returns the maximum of the fields. |
|
minof | Returns the minimum of the fields. |
|
noFetch | Indicates that no records are to be fetched at present. This is typically used when the result of the select is passed on to another application object, for example, a query that performs the actual fetch. |
|
notExists | Chosen only if there are no posts. |
|
optimisticLock | Forces a statement to run with Optimistic Concurrency Control even if a different value is set on the table. For more information, see Optimistic Concurrency Control. |
|
order by | Instructs the database to sort the selected records by fields in the |
|
outer | outer join. Can typically display all rows that both do and do not meet the conditions. An outer join can display all non-matching rows from either the first-named table or second-named table, or from both tables together. |
|
pessimisticLock | Forces a statement to run with Pessimistic Concurrency Control even if a different value is set on the table. For more information, see Optimistic Concurrency Control. |
|
repeatableRead | Specifies that no other transactions can modify data that has been read by logic inside the current transaction, until after the current transaction completes. An explicit transaction completes at either ttsAbort or at the outermost ttsCommit. For a stand-alone select statement, the transaction duration is the duration of the select command. However, the database sometimes enforces the equivalent of repeatableRead in individual select statements even without this keyword appearing in your X++ code (depending on how the database decides to scan the tables). | For more information, see the documentation for the underlying relational database product. |
reverse | Records are returned in reverse order. |
|
sum | Returns the sum of the fields. Can be used to sum all accounts, order lines, and so on. |
|