order by Name asc;

;

select avg(value) from custTable;

print custTable.value;

int64 iCountRows; ;

Select COUNT(RecID) from xCT;

iCountRows = xCT.RecID;

container conCompanies = [‘dat’,’dmo’];

;

select crossCompany :conCompanies

    * from custTable;

    order by Name desc;

    order by AccountNum

    exists join * from ctr

    where (ctr.AccountNum ==

      custTable.AccountNum)

    order by AccountNum;

    ItemId   itemId,

    boolean  update = false)

{

    InventTable inventTable;

    ;

    inventTable.selectForUpdate

        (update);

    if (itemId)

    {

         select firstonly inventTable

            index hint ItemIdx

            where inventTable.itemId

                == itemId;

    }

    return inventTable;

}

Note

You are advised not to use the forceLiterals keyword in X++ selectstatements, because it could expose code to an SQL injection security threat.

forceLiterals instructs the kernel to reveal the actual values that are used in whereclauses to the Microsoft SQL Server database at the time of optimization.

forceLiterals and forcePlaceholders are mutually exclusive.

    forceNestedLoop inventTransThis

    index hint TransIdIdx

    where inventTransThis.InventTransId

        == inventTrans.InventTransId

        && inventTransThis.StatusIssue

        <= StatusIssue::ReservOrdered

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.

{

    SalesTable salesTable;

    SalesLine salesLine;

    ;

    while select forcePlaceholders salesLine

        join salesTable

           where salesTable.SalesId ==

            salesLine.SalesId

               && salesTable.SalesId == ’10’

    {

        //more code

    }

}

{

    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;

}

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;

;

while select sum(CreditMax) from custTable

    group by CustGroup

{

    print custTable.CustGroup, ” “,custTable.CreditMax;

}

;

while select AccountNum, Name from custTable

    index AccountIdx

{

    print custTable.AccountNum, ” “, custTable.Name;

}

    index hint NumVoucherIdx

    where ledgerJournalTrans.journalNum

        == _journalNum

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.

    join ledgerTrans

        where ledgerTrans.accountNum ==

            ledgerTable.accountNum

    {

        amountMST += ledgerTrans.amountMST;

    }

;

select maxof(CreditMax) from custTable;

;

select minof(CreditMax) from custTable;

    order by AccountNum

    order by AccountNum

    notExists join * from ctr

    where (ctr.AccountNum ==

        custTable.AccountNum)

For more information, see Optimistic Concurrency Control.

    where custTable.AccountNum > ‘1000’

    order by accountNum desc

    where custTable.AccountNum > “100”;

For more information, see Optimistic Concurrency Control.

    where custTable.AccountNum > ‘1000’;

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 selectstatements even without this keyword appearing in your X++ code (depending on how the database decides to scan the tables).

    order by AccountNum;

;

select sum(CreditMax) from custTable;