Dynamics 365 FO - Accessing the database - Practice
Nghia Song - Microsoft Dynamics 365 Technical Consultant
Nghia Song
Tel - WhatsApp: +84967324794
Email: songnghia.uit@gmail.com
Select statement examples
3.1.1 Example select statements
The following X++ job shows several small examples of how you can use the select statement.
static void SelectRecordExamples3Job(Args _args)
{
CustTable custTable; // As of AX 2012.
// A customer is found and returned in custTable
select * from custTable;
info("A: " + custTable.AccountNum);
// A customer with account number > "100" is found
select * from custTable
where custTable.AccountNum > "100";
info("B: " + custTable.AccountNum);
// Customer with the lowest account number > "100" found:
select *
from custTable
order by accountNum
where custTable.AccountNum > "100";
info("C1: " + custTable.AccountNum);
// The next customer is read
next custTable;
info("C2: " + custTable.AccountNum);
// Customer with higest account number
// (greater than 100) found: Fourth Coffee
select *
from custTable
order by accountNum desc
where custTable.accountNum > "100";
info("D1: " + custTable.AccountNum);
// The next record is read (DESC): Fabrikam, Inc.
next custTable;
info("D2: " + custTable.AccountNum);
// Customer with highest account number found: Fourth Coffee
select reverse custTable
order by accountNum;
info("E: " + custTable.AccountNum);
// Customer with "lowest" name and account number
// in the interval 100 to 1000 is found. This is Coho Winery.
select *
from custTable
order by DlvMode
where custTable.accountNum > "100"
&& custTable.accountNum < "1000";
info("F: " + custTable.AccountNum);
// The count select returns the number of customers.
select count(AccountNum)
from custTable;
// Prints the result of the count
info(strFmt("G: %1 = Count of AccountNums", custTable.accountNum));
// Returns the average credit max for non-blocked customers.
select avg(CreditMax)
from custTable
where custTable.blocked == CustVendorBlocked::No;
// Prints the result of the avg
info(strFmt("H: %1 = Average CreditMax", custTable.CreditMax));
}
/*** Display from infolog:
Message (02:00:34 pm)
A: 4000
B: 4000
C1: 4000
C2: 4001
D1: 4507
D2: 4506
E: 4507
F:
G: 29 = Count of AccountNums
H: 103.45 = Average CreditMax
***/
Join sample
This X++ code sample shows how an inner join can be performed as part of an SQL select statement.
The sample also shows an order by clause that has each field qualified by a table name. This enables you to control how the retrieved records are sorted by using only one order by clause.
static void SelectJoin22Job(Args _args)
{
CustTable xrecCustTable;
CashDisc xrecCashDisc;
struct sut4;
sut4 = new struct("str AccountNum; str CashDisc; str Description");
while select firstOnly10 *
from xrecCustTable
order by xrecCashDisc.Description
join xrecCashDisc
where xrecCustTable.CashDisc ==
xrecCashDisc.CashDiscCode
&& xrecCashDisc.Description LIKE "*Days*"
{
sut4.value("AccountNum", xrecCustTable.AccountNum );
sut4.value("CashDisc", xrecCashDisc.CashDiscCode );
sut4.value("Description", xrecCashDisc.Description );
info(sut4.toString());
}
/********* Actual Infolog output
Message (02:29:37 pm)
(AccountNum:"1101"; CashDisc:"0.5%D10"; Description:"0.5% 10 days")
(AccountNum:"4001"; CashDisc:"0.5%D10"; Description:"0.5% 10 days")
(AccountNum:"1102"; CashDisc:"0.5%D30"; Description:"0.5% 30 days")
(AccountNum:"1201"; CashDisc:"0.5%D30"; Description:"0.5% 30 days")
(AccountNum:"2211"; CashDisc:"0.5%D30"; Description:"0.5% 30 days")
(AccountNum:"1202"; CashDisc:"1%D15"; Description:"1% 15 days")
(AccountNum:"1203"; CashDisc:"1%D07"; Description:"1% 7 days")
(AccountNum:"2212"; CashDisc:"1%D07"; Description:"1% 7 days")
(AccountNum:"2213"; CashDisc:"1%D07"; Description:"1% 7 days")
(AccountNum:"2214"; CashDisc:"1%D07"; Description:"1% 7 days")
*********/
}
Group by and Order by
This X++ code sample shows that the fields in the group by clause can be qualified with a table name. There can be multiple group by clauses instead of just one. The fields can be qualified by table name in only one group by clause. Use of table name qualifiers is recommended.
The order by clause follows the same syntax patterns that group by follows. If provided, both clauses must appear after the join (or from) clause, and both must appear before the where clause that might exist on the same join. It is recommended that all group by and order by and where clauses appear immediately after the last join clause.
static void SelectGroupBy66Job(Args _args)
{
CustTable xrecCustTable;
CashDisc xrecCashDisc;
struct sut4;
sut4 = new struct("str AccountNum_Count; str CashDisc; str Description");
while select
count(AccountNum)
from xrecCustTable
order by xrecCashDisc.Description
join xrecCashDisc
group by
xrecCashDisc.CashDiscCode
where xrecCustTable.CashDisc ==
xrecCashDisc.CashDiscCode
&& xrecCashDisc.Description LIKE "*Days*"
{
sut4.value("AccountNum_Count", xrecCustTable.AccountNum );
sut4.value("CashDisc", xrecCashDisc.CashDiscCode );
sut4.value("Description", xrecCashDisc.Description );
info(sut4.toString());
}
/********* Actual Infolog output
Message (02:45:26 pm)
(AccountNum_Count:"2"; CashDisc:"0.5%D10"; Description:"")
(AccountNum_Count:"3"; CashDisc:"0.5%D30"; Description:"")
(AccountNum_Count:"4"; CashDisc:"1%D07"; Description:"")
(AccountNum_Count:"1"; CashDisc:"1%D15"; Description:"")
(AccountNum_Count:"1"; CashDisc:"2%D30"; Description:"")
(AccountNum_Count:"1"; CashDisc:"3%D10"; Description:"")
*********/
}
Select statement with an Outer Join
In Microsoft Dynamics AX, the X++ SELECT statement supports filtering an OUTER JOIN in the WHERE clause. In the JOIN clause of standard SQL there is an ON keyword for filter criteria. But there is no such ON keyword in X++ SQL.
An inner join rejects all table rows that fail to match a row in the other joined table. But an outer join includes rows from the first table even though there is no matching row in the other joined table. Default values are substituted for the data that could not be obtained from a matching row in the other joined table.
static void OuterJoinSelectJob3(Args _args)
{
SalesOrder recSalesOrder;
SalesOrderLine recSalesOrderLine;
struct struct4;
;
struct4 = new struct
("int SalesOrderID;"
+ "date DateAdded;"
+ "str SalesOrderLineID;"
+ "int Quantity"
);
while
SELECT
from
recSalesOrder
OUTER JOIN recSalesOrderLine
WHERE
recSalesOrder.SalesOrderID == recSalesOrderLine.SalesOrderID
&& recSalesOrderLine.Quantity == 66
{
struct4.value("SalesOrderID", recSalesOrder.SalesOrderID);
struct4.value("DateAdded", recSalesOrder.DateAdded);
struct4.value("SalesOrderLineID", recSalesOrderLine.SalesOrderLineID);
struct4.value("Quantity", recSalesOrderLine.Quantity);
info(struct4.toString());
}
}
While select Statements
while select statements are used to handle data. They are the most widely used form of the select statement in X++.
static void JobWhileSelect(Args _args) // X++ job.
{
int iCounter = 0;
BankAccountTable xrecBAT;
while select * from xrecBAT
where iCounter < 1
{
iCounter++;
Global::info(strFmt("%1 , %2", iCounter, xrecBAT.AccountID));
}
}
/*** Display from infolog:
Message (04:59:38 pm)
1 , Cash1
2 , STB-DKK
3 , STB-EUR
***/
Printing a sorted Telephone List for Some Customers
static void JobPrintTel(Args _args)
{
CustTable xrecCT;
while select xrecCT
order by xrecCT.AccountNum
where xrecCT.AccountNum >= "4010"
&& xrecCT.AccountNum <= "4100"
{
Global::info(strFmt("%1 , %2",
xrecCT.AccountNum, xrecCT.SalesGroup));
}
}
/*** Display from Infolog:
Message (06:04:03 pm)
4010 , CSG-EU
4011 , CSG-EU
4012 , CSG-OTH
4013 , CSG-OTH
4014 , CSG-OTH
4015 , CSG-OTH
4016 , CSG-EU
4017 , CSG-EU
4018 , CSG-EU
4020 ,
4024 ,
***/
Index and Order by in select statement
Example
SalesTable salesTable;
select salesTable
index hint CustIdx
order by CustAccount
where salesTable.CustAccount >= '3000'
&& salesTable.CustAccount <= '4000'
&& salesTable.FixedDueDate >= 12\12\2004
&& salesTable.FixedDueDate <= 05\05\2009;
Data Manipulation
Data manipulation in X++ refers to interactively using SQL commands. These commands include insert, update and delete
These are required to modify the data in the database. When values are assigned to a table buffer, the data is not updated in the database.
Insert
- To create new records in the database, use the insert() method on the table. The data is first set on the table buffer by assigning values to fields. The data is then committed to the database by calling the insert() method.
CustTable custTable;
//1st
custTable.clear();
custTable.AccountNum = ‘1234567’;
custTable.Address = ‘Binh Phuoc’;
custTable.insert();
//2nd
custTable.clear();
custTable.accountNum = "1234";
custTable.Currency = "USD";
custTable.insert();
doInsert
To override the behavior of update, use the do update method.
Insert_Recordset
Insert_Recordset copies data from one or more tables in one call to the database.
This is much faster than selecting records individually and then inserting the new
records into the table.
VendTable vendTable;
HcmWorker hcmWorker;
Insert_RecordSet vendTable (accountnum, Party)
select PersonnelNumber, person from HcmWorker;
Update
The Update command modifies existing data in a table with the contents of a table buffer. The record is first retrieved from the database using a select statement. The data is then modified by assigning the new values to the fields in the table buffer. The new values are then committed to the database using the update() method.
Before records can be updated, use select forUpdate to exclusively set a record for update. This tells SQL that the record is to be updated and allows the database to lock the record so that another user cannot modify it at the same time.
SalesTable salesTable;
ttsbegin;
while select forupdate salesTable
where salesTable.CustAccount =="2001"
{
salesTable.SalesName ="New Enterprises";
salesTable.update();
}
ttscommit;
NOTE: The use of ttsbegin and ttscommit. This indicates the start and end of a
transaction.
doUpdate
The doUpdate method should be used when the update method on the table is to be bypassed.
Update_Recordset
The update_recordset command allows the manipulation of many records in one
operation. This command speeds up database processing because of fewer calls
to the database
SalesTable salesTable;
ttsbegin;
update_recordset salesTable
setting salesName ="New Enterprises"
where salesTable.CustGroup =="ABC";
ttscommit;
Delete
The delete command deletes a complete record from the database that meets the condition of the select statement
CustTable custTable;
ttsbegin;
Select forUpdate custTable
where custTable.accountnum =="2032";
custTable.delete();
ttscommit;
Delete_from
CustTable custTable;
delete_from custTable
where custTable.Currency == "ABC";
The delete_from command removes multiple records from the database at one
time. Similar to the update_recordset command, delete_from consolidates
many databases calls into one operation and increases database performance.
Transaction Integrity Checking
t is important to ensure the integrity ofall transactions within the system. When a transaction begins, to ensure data consistency, it must finish completely with predictable results. If the transaction terminates in the middle, the system should roll back to its state before the transaction began. Use the Transaction Tracking System (tts) to help you ensure database integrity.
The following keywords help in integrity checking:
ttsbegin: Indicates the beginning of the transaction.
ttscommit: Indicates the successful end of a transaction. This ensures the transaction is performed as intended upon completion.
ttsabort: Used as an exception to abort and roll back a transaction to the state before the ttsbegin.
Query
A query is an application object in the AOT. A query performs the same function as the select statements. It is a better option because it allows for more flexible user interaction when defining which records are to be retrieved. Queries provide more flexibility, especially when sorting and specifying ranges.
Executing a Query in X++
The Query() class does not "fetch" records, this is accomplished by using the QueryRun() class. The Query() class provides the framework for the query whereas the QueryRun() class starts this framework dynamically. The following example creates and runs a simple query.
The QueryStr() function validates that the element of type Query called Cust exists in the AOT.
Query query = new Query (QueryStr(Cust));
// Use the query to build a queryRun object
QueryRun queryRun = new QueryRun (query);
// Traverse some records...
while (queryRun.next())
{
// ...
}
Building a Query in X++
Queries contain many important elements. These elements have been discussed in earlier courses in the context of the AOT. This section discusses these elements from within the context of X++. Elements include datasources, ranges, and sort fields which build upon each other.
There are two more classes to note before building a query:
QueryBuildDataSource
QueryBuildRange
QueryBuildDataSource
Data sources are what queries are built upon. They are arranged in a hierarchy and define the sequence in which records are fetched from tables assigned to the data source. The following example adds a data source to a query using X++
Query query = new Query();
QueryBuildDataSource qbds_SalesTable = query.addDataSource(TableNum(SalesTable));
Notice that the data source is an object, but the query object, 'query', requires a method to add this data source to the query and assign the SalesTable to the data source.
QueryBuildRange
A QueryBuildRangeobject is embedded within a data source of a query and defines which records should be selected from the data source. A querybuild range is built upon a QueryBuildDataSourceobject. The following example uses the QueryBuildRange:
Query query = new Query();
QueryBuildDataSource qbds_SalesTable = query.addDataSource(TableNum(SalesTable));
QueryBuildRange qbr_CustAccount = qbds_SalesTable.addRange(FieldNum(SalesTable,CustAccount));
qbr_CustAccount.value(“10000”);
Example: Building a Query in X++
The following code demonstrates how a query is built from scratch in X++. This query returns all sales orders, from the SalesTable, for customer '4008', sorted by the SalesId.
Query query;
QueryRun queryRun;
QueryBuildDataSource qbds;
QueryBuildRange qbr;
SalesTable SalesTable;
query = new Query();
//this line attaches a table to the qbds data source object
qbds = query.addDataSource(TableNum (SalesTable));
//this line attaches a range to the 'SalesTable' //data source, the range is the CustAccount
If(CustAccountFilter != “”)
{
qbr = qbds.addRange(FieldNum (SalesTable,CustAccount)); // The range is set to '2001'
qbr.value ('2001');
}
// The query will sort by sales id
qbds.addSortField (FieldNum(SalesTable,SalesId));
// The queryRun object is instantiated using the query
queryRun = new QueryRun(query);
// The queryRun object loops through all records returned
while (queryRun.next())
{
// The current record is assigned to the salesTable variable
salesTable = queryRun.get(tableNum(SalesTable));
Info(salesTable.SalesId);
}
Display field
This is a field to lookup another table based on current record
Next: Dynamics 365 FO - Accessing the database - Sample Code
https://www.songnghia.com/2022/09/dynamics-365-fo-accessing-database_22.html
No comments:
Post a Comment