preloader-matrix

Alfasith

SQL tutorial for X++ / Dynamic AX

Hi,SQL tutorial for AX buds.
SELECT – extracts data from a database
UPDATE – updates data in a database
DELETE – deletes data from a database
INSERT INTO – inserts new data into a database
CREATE DATABASE – creates a new database
ALTER DATABASE – modifies a database
CREATE TABLE – creates a new table
ALTER TABLE – modifies a table
DROP TABLE – deletes a table
CREATE INDEX – creates an index (search key)
DROP INDEX – deletes an index
————————————–
Consider this below table as example for upcoming scenarios.
                  Student————————————–
studentID
Name
Gender
Native
Stu001
Fathima
Female
IND
Stu002
Faridth
Male
IND
Stu003
Faheed
Male
KSA
Stu004
Fakrudeen
Male
KSA
Stu005
Firdose
Female
IND
Stu006
Fahima
Female
KSA
—————————————
All the SQL syntax is not applicable for X++;
SQL Tutorial
SQL Select
Basic select concern record
while SELECT field_Name
FROM table_name
SQL Distinct
List unique records
while SELECT DISTINCT
field_Name FROM table_name
SQL Where
Where condition
while SELECT field_Name
FROM table_name where Table_Name.Field_Name ==/=< conditions
SQL And & Or
Show only IND female student
while SELECT StudentID
FROM Student where  Student.Gender == Gender::Female && Student.Native == “IND”
Show all female record, IND native also
while SELECT StudentID
FROM Student where  Student.Gender == Gender::Female || Student.Native == “IND”
SQL Order By
Sort the record
while SELECT DISTINCT
field_Name FROM table_name ORDER BY table_name.field_Name ASC/DESC
SQL Insert Into
MyTable  MyTable;  ttsBegin;
select MyTable ;  MyTable.AccountNum = ‘1101’; MyTable.Name = ‘MyName’;  MyTable.insert();  ttsCommit;
SQL Update
Student Student; ttsBegin;  select forUpdate Student where Student.StudentID= “Stu0002”; Student.StudentID = ‘Stu0009’; Student.Name = “Rahima”; Student.update();  ttsCommit;
SQL Delete
static void DeleteMultiRow1bJob(Args _args)
{
    MyWidgetTable tabWidget; // extends xRecord.
    ;
    ttsBegin;
    while select
        forUpdate
        tabWidget
        where tabWidget .quantity <= 100
    {
        tabWidget .delete();
    }
    ttsCommit;
}
SQL Select Top
Code fastup the search and once found it will search for remaining data
select FirstOnly PersonnelNunber where HcmWorker.PersonnelNumber == “0000567”;
SQL Between
There is no between in X++ but
while select Table_Name where Table_Name.Field_Name >= RangeFromValue && Table_Name.Field_Name <= RangeToValue
SQL Wildcards
while SELECT DISTINCT field_Name FROM table_name
SQL Not Null
while select Table_Name where Table_Name.Field_Name != Null
—————————————————————————————–
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
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. (Acontainer 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 custTableorder by Name desc;
exists
A method that returns a Boolean value and a join clause.
while select AccountNum,Name from cust Table 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 inventTableindex 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
 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
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 forceSelectOrderkeyword.
while select forceSelectOrder
    forceNestedLoopinventTransThis
    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.
static void forcePlaceHoldersExample(Args _args)
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
   }
}
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
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 ororder by clauses, and where the effect of the hint can be verified.
 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.
while select ledgerTable
Reduces the number of SQL statements that are needed if you want to loop through a table and update transactions in a related table.
join ledgerTrans
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.
 where ledgerTrans.accountNum ==
  ledgerTable.accountNum
 {
amountMST += ledgerTrans.amountMST;
 }
maxof
Returns the maximum of the fields.
ustTable custTable;
;
select maxof(CreditMax) from custTable;
minof
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.
select optimisticLock custTable
   where custTable.AccountNum > ‘1000’
For more information, see Optimistic Concurrency Control.
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 leftkeyword. There is no right outer join in X++ SQL.
X++
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.
select pessimisticLock custTable
For more information, see Optimistic Concurrency Control.
    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 outermostttsCommit.
For a stand-alone select statement, the transaction duration is the duration of theselect command. However, the database sometimes enforces the equivalent ofrepeatableRead 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 custTableorder 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.
X++ 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); }
Ref : http://msdn.microsoft.com/en-us/library/aa656402.aspx

Leave a Reply

Your email address will not be published.