Select Statement Syntax for AX
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
|
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
|
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 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 join statements.
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.
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.
|
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 clause 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, Name
from
custTable
order by
AccountNum
outer
join * from ctr
where
ctr.AccountNum == custTable.AccountNum
|
pessimisticLock
|
Forces a statement to run with
Pessimistic Concurrency Control even if a different value is set on the
table.
|
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 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.
|
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;
|
select containerLine.*
from WHSCONTAINERLINE containerLine
inner join WHSWORKTABLE workTable on workTable.CONTAINERID = containerLine.CONTAINERID
inner join WHSLOADLINE whsloadline on whsloadline.RECID = containerLine.LOADLINE
inner join WHSWORKINVENTTRANS trans on trans.INVENTTRANSIDPARENT = whsloadline.INVENTTRANSID and trans.WORKID = workTable.WORKID
where containerLine.CONTAINERID = '0000156938'
No comments:
Post a Comment