Select Statement Syntax for AX

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;

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 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;