preloader-matrix

Alfasith

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
Keywords Used in the Select Syntax

 

 

Keyword
Description
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
NoteNote
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;
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.
NoteNote
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 ledgerJournal
Trans.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;
Share on email
Share on facebook
Share on twitter
Share on linkedin
Share on pinterest

Leave a Reply

Your email address will not be published.