Applies To: Microsoft Dynamics AX 2012 R2, Microsoft Dynamics AX 2012 Feature Pack, Microsoft Dynamics AX 2012
SelectStatement
|
=
|
select Parameters
|
Parameters
|
[ [ FindOptions ] [ FieldList from ] ] TableBufferVariable [ IndexClause ] [ Options ] [ WhereClause ] [ JoinClause ]
| |
FindOptions
|
=
|
crossCompany | reverse | firstFast | [ firstOnly | firstOnly10 | firstOnly100 | firstOnly1000 ] | forUpdate | noFetch | [forcePlaceholders |forceLiterals] | forceselectorder | forceNestedLoop | repeatableRead | validTimeState
|
FieldList
|
=
|
Field { , Field } | *
|
Field
|
=
|
Aggregate ( FieldIdentifier ) | FieldIdentifier
|
Aggregate
|
=
|
sum | avg | minof | maxof | count
|
Options
|
=
|
[ order by , group by , FieldIdentifier [ asc | desc ] { , FieldIdentifier [ asc | desc ] }] | [ IndexClause ]
|
IndexClause
|
=
|
index IndexName | index hint IndexName
|
WhereClause
|
=
|
where Expression
|
JoinClause
|
=
|
[exists | notexists | outer ] join Parameters
|
Keyword
|
Description
|
Example
| ||
|---|---|---|---|---|
asc
|
An option on the order by or group by clause. The sorting is ascending. (Sort is ascending by default.)
|
select * from custTable
order by Name asc;
| ||
avg
|
Returns the average of the fields.
|
CustTable custTable;
;
select avg(value) from custTable;
print custTable.value;
| ||
count
|
Returns the number of records.
|
CustTable xCT;
int64 iCountRows; ;
Select COUNT(RecID) from xCT;
iCountRows = xCT.RecID;
| ||
crossCompany
|
Returns data for all companies that the user is authorized to read from. (A container can be added to reduce the number of companies involved.)
|
CustTable custTable;
container conCompanies = ['dat','dmo'];
;
select crossCompany :conCompanies
* from custTable;
| ||
desc
|
An option on the order by or group by clause. The sorting is descending.
|
select * from custTable
order by Name desc;
| ||
exists
|
A method that returns a Boolean value and a join clause.
|
while select AccountNum, Name from custTable
order by AccountNum
exists join * from ctr
where (ctr.AccountNum ==
custTable.AccountNum)
| ||
firstFast
|
A priority hint. The first row appears more quickly but the total return time for this option might be slower. The firstFast hint is automatically issued from all forms, but is rarely used directly from X++.
|
select firstFast custTable
order by AccountNum;
| ||
firstOnly
|
Speeds up the fetch. Instructs MorphX to fetch only the first record.
|
static InventTable find(
ItemId itemId,
boolean update = false)
{
InventTable inventTable;
;
inventTable.selectForUpdate
(update);
if (itemId)
{
select firstonly inventTable
index hint ItemIdx
where inventTable.itemId
== itemId;
}
return inventTable;
}
| ||
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
|
forceLiterals instructs the kernel to reveal the actual values that are used in where clauses to the Microsoft SQL Server database at the time of optimization.
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.
|
while select forceSelectOrder
forceNestedLoop inventTransThis
index hint TransIdIdx
where inventTransThis.InventTransId
== inventTrans.InventTransId
&& inventTransThis.StatusIssue
<= StatusIssue::ReservOrdered
| ||
forcePlaceholders
|
Instructs the kernel not to reveal the actual values used in where clauses to the SQL Server database at the time of optimization. This is the default in all statements that are not joinstatements.
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.
|
static void forcePlaceHoldersExample(Args _args)
{
SalesTable salesTable;
SalesLine salesLine;
;
while select forcePlaceholders salesLine
join salesTable
where salesTable.SalesId ==
salesLine.SalesId
&& salesTable.SalesId == '10'
{
//more code
}
}
| ||
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.
|
display ForecastHasPurch hasForecastPurch()
{
ForecastPurch forecastPurch;
InventDim inventDim;
;
select firstOnly forcePlaceholders
forceSelectOrder recId
from forecastPurch
index hint ItemIdx
where forecastPurch.itemId == this.itemId
exists join inventDim
index hint DimIdIdx
where inventDim.inventDimId ==
forecastPurch.inventDimId
&& inventDim.configId == this.configId;
return forecastPurch.recId;
}
| ||
forUpdate
|
Selects records exclusively for update. Depending on the underlying database, the records may be locked for other users.
|
ttsBegin;
while select forUpdate ledgerJournalTrans
index hint NumVoucherIdx
where ledgerJournalTrans.journalNum ==
_journalNum &&
ledgerJournalTrans.voucher == _voucher
{
ledgerJournalTrans.doDelete();
counter++;
}
if (counter
&& ledgerJournalTable.journalType
!= LedgerJournalType::Periodic)
{
NumberSeq::release(
ledgerJournalTable.voucherSeries,
_voucher);
}
ttsCommit;
| ||
group by
|
Instructs the database to group selected records by fields.
|
CustTable custTable;
;
while select sum(CreditMax) from custTable
group by CustGroup
{
print custTable.CustGroup, " ",custTable.CreditMax;
}
| ||
index
|
Instructs the database to sort the selected records as defined by the index.
|
CustTable custTable;
;
while select AccountNum, Name from custTable
index AccountIdx
{
print custTable.AccountNum, " ", custTable.Name;
}
| ||
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.
|
while select forUpdate ledgerJournalTrans
index hint NumVoucherIdx
where ledgerJournalTrans.journalNum
== _journalNum
| ||
join
|
Used to join tables on a column that is common to both tables. The join criteria are specified in the where clause because there is no on keyword in X++ SQL.
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 while select to do this, there will be 501 trips to the database. If you use a join, there will be a single trip to the database.
|
while select ledgerTable
join ledgerTrans
where ledgerTrans.accountNum ==
ledgerTable.accountNum
{
amountMST += ledgerTrans.amountMST;
}
| ||
maxof
|
Returns the maximum of the fields.
|
CustTable custTable;
;
select maxof(CreditMax) from custTable;
| ||
minof
|
Returns the minimum of the fields.
|
CustTable custTable;
;
select minof(CreditMax) from custTable;
| ||
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.
|
select noFetch custTable
order by AccountNum
| ||
notExists
|
Chosen only if there are no posts.
|
while select AccountNum, Name from custTable
order by AccountNum
notExists join * from ctr
where (ctr.AccountNum ==
custTable.AccountNum)
| ||
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.
|
select optimisticLock custTable
where custTable.AccountNum > '1000'
| ||
order by
|
Instructs the database to sort the selected records by fields in the order by list.
|
select * from custTable
order by accountNum desc
where custTable.AccountNum > "100";
| ||
outer
|
Returns all rows from the first-named table, including rows that have no match in the second-named table. This is a left outer join, although there is no left keyword. There is no right outer join in X++ SQL.
| while select AccountNum
from custTable
order by AccountNum
outer join * from custBankAccount
where custBankAccount.AccountNum ==
custTable.AccountNum
{
print custTable.AccountNum,
" , ", custBankAccount.DlvMode;
}
pause;
| ||
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.
|
select pessimisticLock custTable
where custTable.AccountNum > '1000';
| ||
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 selectcommand. 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.
|
select reverse custTable
order by AccountNum;
| ||
sum
|
Returns the sum of the fields. Can be used to sum all accounts, order lines, and so on.
|
CustTable custTable;
;
select sum(CreditMax) from custTable;
| ||
validTimeState
|
Filters rows from a table that has its ValidTimeStateFieldType property set to a value other than None. For more information, see Valid Time State Tables and Date Effective Data.
| static void VtsJob1(Args _args)
{
// A VTS table in AX 2012.
CustPackingSlipTransHistory xrec1;
utcDateTime myDateFrom , myDateTo;
anytype myAnytype = -1;
myDateFrom = DateTimeUtil::utcNow();
myDateTo = myDateFrom;
SELECT
validTimeState(myDateFrom, myDateTo)
*
FROM xrec1;
myAnytype = xrec1.getFieldValue("RecId");
info(myAnytype);
}
|
Note
Aucun commentaire:
Enregistrer un commentaire