Search through blog..

Thursday, October 18, 2012

X++ Implementation of few SQL Keywords


As we already saw in the Previous post, In X++, we can directly use database related statements similar to any other code. Here, we will see and understand how to implement few well-known SQL keywords in X++.

Example(1): To print records in custTable in descending order.
while select custTable order by custTable.AccountNum desc
{  
print(strfmt("%1, %2", custTable.accountNum, custTable.Address));   
}
pause;
Output:


By using order by clause similar to the SQL queries with the keyword ‘desc’, we are able to achieve the expected output.


Example(2): To print Average of Amount from custTrans table.
while select avg(AmountMST) from custTrans
{  
print(custTrans.AmountMST);   
}
pause;
Output:


Here, we are using while select statement with the Aggregate keyword ‘avg’ on the required field to get the Average of all the values of AmountMST in the table.
Also note that to print the output, You can get the result from the same field, i.e, custTrans.AmountMST.

The below are Details, Syntax and an AX Example respectively for few well-known SQL keywords:





Keyword Example
asc Set the sorting order to ascending. All selects are default fetching data ascending.
Syntax: select custTable order by accountNum asc;
 
desc Set the sorting order to descending. Used in combination with order by or group by.
Syntax: select custTable order by name desc;
AX Example: See table method CustTable.lastPayment().
 
avg Select uses aggregate keyword (avg) using only one call to the database calculating a result based on multiple records
Syntax: select avg(amountMST) from custTrans;
AX Example: See class method KMKnowledgeCollectorStatisticsExecute.runQuery().
 
count Aggregate keyword used to count the number of records fetched.
Syntax: select count(recId) from custTrans;
AX Example: See class method KMKnowledgeCollectorStatisticsExecute.runQuery().
 
sum Aggregate keyword used to sum values of a field fetched.
Syntax: select sum(amountMST) from custTrans;
AX Example: See class method KMKnowledgeCollectorStatisticsExecute.runQuery().
 
maxof Aggregate keyword used to return the highest field value fetched
Syntax: select maxOf(amountMST) from custTrans;
AX Example: See class method KMKnowledgeCollectorStatisticsExecute.runQuery().
 
minof Aggregate keyword used to return the lowest field value fetched.
Syntax: select minOf(amountMST) from custTrans;
AX Example: See class method KMKnowledgeCollectorStatisticsExecute.runQuery().
 
delete_from Will delete multiple records in one call to the database.
Syntax: delete_from myTable where myTable.amountMST <='1000';
AX Example: See class method InventCostCleanUp.updateDelSettlement().
 
exists join Exists join is used to fetch records where at least one record in the secondary table matches the join expression.
No records will be fetched from the secondary table using exists join.
Syntax: while select custTable exists join custTrans
where custTable.accountNum == custTrans.accountNum
AX Example: See class method InventAdj_Cancel.cancelInventSettlements().
 
notexists join Opposite of exists join. Will fetch records from the primary table, where no records in the secondary table match the join expression.
Syntax: while select custTable notexists join custTrans
where custTable.accountNum == custTrans.accountNum
AX Example: See class method InventConsistencyCheck_Trans.run().
 
outer join Outer join will select records from both tables regardless if there are any records in the secondary table matching the join expression.
Syntax: while select custTable outer join custTrans
AX Example: See class method SysHelpStatistics.doTeams().
 
join Join will fetch Records matching the join expressionfrom both tables. (innerjoin)
Syntax: while select custTable join custTrans
where custTable.accountNum == custTrans.accountNum
AX Example: See table method SalesTable.LastConfirm().
 
firstfast Instruct to select the first record faster. used in situations where only one record is shown, like in a dialog.
Syntax: select firstfast custTable order by accountNum;
AX Example: See class method ProjPeriodCreatePeriod.dialog().
 
firstonly First record will be selected. Firstonly should always be used when not using while in selects.
Syntax: select firstonly custTable where custTable.AccountNum == _custAccount (variable)
AX Example: See Table method CustTable.find().
 
forupdate Used If records in a select are to be updated
Syntax: while select forupdate reqTransBOM where reqTransBOM.ReqPlanId     ==  this.ReqPlanId
AX Example: See Table method ReqTrans.deleteExplosionCoverage().
 
from Default all fields of a table is selected. From is used to select only the fields specified.
Use it for optimization only, as it makes the code more complex.
Syntax: select accountNum, name from custTable;
 
group by Sort the fetched data group by the fields specified. Only the fields specified in the group by will be fetched.
Syntax: while select custTable group by custGroup;
AX Example: See class method InventStatisticsUS.calcTotals().
 
index Used to set the sorting order of the fetched data. The kernel will convert the keyword index to an order by using the fields from the index.
Index should only be used if the fetched data must be sorted in a specific way, as the database will choose a proper index.
Syntax: while select custTable index accountIdx.
 
index hint Index hint will force the database to use the specified index.
Syntax: while select custTable index hint accountIdx.
AX Example: See Table method ReqTrans.deleteExplosionCoverage().
 
insert_recordset Used to insert multiple records in a table. Insert_recordset is useful when copying data from one table to another as it only requires one call to the database
Syntax: insert_recordset myTable (myNum,mySum)
select myNum, sum(myValue) from anotherTable group by myNum where myNum <= 100;
AX Example: See class method SysLicenseCodeReadFile.handleDomainLicenseChanges().
 
update_recordset Used  to update multiple records in one database call. Useful to initialize fields in a fast way.
The fields updated are specified after thekeyword setting.
Syntax: update_recordset myTable setting field1 = myTable.field1 * 1.10;
AX Example: See class method ProdUpdHistoricalCost.postScrap().

Parent Link: Using Select statement in X++ to retrieve data 

2 comments:

Ajit said...

Hi Christi.. Thanks for your comment!!
And about SQL Training, there could be two scenarios. First, if you are looking for SQL training only to work with Microsoft Dyanimcs Axapta? Then I personally feel that you don't need to go for the online live classes training. Instead you can go through information avaliable in Internet and concentrate on SQL joins.
But if you are looking for a core SQL training to enable yourself to understand in and out of SQL.. then you might want to go for the training link you posted.
hope this help..

Unknown said...

HI Ajith ,

Thanks lot its very use full the person who wants to work in X++ as a technical developer or supporter.
thanks for sharing your valuable knowledge, i hope you will post more advanced queries which uses in real time.

Thanks
Santhosh P