Skip to main content

Posts

Showing posts with the label query

All about queries

The query object model contains classes to define and run a query. These objects are used to define the query data source, the fields returned, record ranges and relations to child data sources. The following illustration shows the object model. The query components shown in the previous figure are system classes. The query classes are more visible when you create a dynamic query in code, but they are also used behind the scenes when you create a static query in the AOT. System class Description QueryRun Executes the query and fetches the data. Query The top level of the query definition. This class holds some properties itself and has one or more related data sources. QueryBuildDataSource Defines access to a single data source in the query. If more than one data source exists at the same level in a query, they result in separate SQL statements that are executed sequentially. If one data source exists as a child of another data source, a join is created between the two data so...
Read more

Dynamics Ax Query Extended Range Value Expression

In this article we will see how to define extended query ranges using expressions. These expressions can be used in any query where you need to express a range that is more complex than is possible with the usual range value notations. The rules for creating query range value expressions are: Enclose the whole expression in parentheses. Enclose all subexpressions in parentheses. Use the relational and logical operators available in X++. Only use field names from the range's data source. Use the dataSource.field notation for fields from other data sources in the query. Values must be constants in the expression, so any function or outside variable must be calculated before the expression is evaluated by the query. This is typically done by using the strFmt function. Let's do it with some examples: OR clause on same field: Query q; QueryBuildDataSource qbd; QueryBuildRange qbr; q = new Query(); qbd = q.addDataSource(TableNum(CustTable)); q...
Read more

QueryRange fucktions

i. SysQuery::value(); ii. SysQuery::valuenot(); iii. SysQuery::valueEmptyString() iv. SysQuery::valueNotEmptyString() v. SysQuery::range() vi. SysQuery::findOrCreateRange(…) vii. QueryRange() viii. QueryValue()
Read more

Query: empty records, not empty records, use enum at query value

Not empty records: qbds.addRange(fieldnum(custtable,name)).value(sysquery::valueNotEmptyString()) ; Empty records: qbds.addRange(fieldnum(custtable,name)).value(sysquery::valueEmptyString()) ; Records are not equal 10000: qbds.addRange(fieldnum(custtable,accountnum)).value(sysquery::valueNot("10000") ) ; Ranges: qbds.addRange(fieldnum(custtable,accountnum)).value(queryrange("1","10000") ) ; Enum: qbds.addRange(fieldnum(B_RMARequestTable, RefType)).value(queryvalue(B_RMAReqRefType::RepairJournal));
Read more

Create LookUps Using X++

public void lookup() { //super(); // Added by Vasanth Arivali // Declaration Query LookupQuery = new Query(); QueryBuildDataSource LookupQueryBuildDataSource; QueryBuildRange LookupQueryBuildRange; SysTableLookup CustomSysTableLookup = SysTableLookup::newParameters(tableNum(InventTable), this); ; // Add fields that you want in Lookups CustomSysTableLookup.addLookupField(fieldNum(InventTable, ItemId)); CustomSysTableLookup.addLookupField(fieldNum(InventTable,ItemName)); CustomSysTableLookup.addLookupField(fieldNum(InventTable,ItemGroupId)); CustomSysTableLookup.addLookupField(fieldNum(InventTable,NameAlias)); CustomSysTableLookup.addLookupField(fieldNum(InventTable,ItemType)); CustomSysTableLookup.addLookupField(fieldNum(InventTable,DimGroupId)); // Add DataSource, Range and Value LookupQueryBuildDataSource =LookupQuery.addDataSource(tableNum(InventTable)); LookupQueryBuildRange=LookupQueryBuildDataSource.addRange(fieldNum(InventTable,ItemVisibili...
Read more

Using AOT Query Object in X++ Code

Steps: 1) Create a AOT Query Object as your requirement. 2) Create a Job and paste the below code to check the Query Object. static void ExecuteAOTQuery(Args _args) { QueryRun queryRun; Counter totalRecords; ; queryRun = new QueryRun(queryStr(CreatedAOTQueryName)); if (queryRun.prompt()) { while (queryRun.next()) { totalRecords++; } } info(strFmt(“Total Records : %1”, totalRecords)); }
Read more

Dynamic Query Using Aggregative Functions and Joins

Here i written the code to use the Aggregate functions and Joins in Dynamic Query in spite of using normal Select statement in Ax 2009 Normal Select Statement: while select sum(qty) from inventTrans where inventTrans.ItemId ==“OL-2500” join inventDimgroup by inventBatchId where inventDim.InventDimId == inventTrans.InventDimId { // Our Code Here } Dynamic Query: static void Vasanth_Query_Eg1(Args _args) { Query query; QueryBuildDataSource queryBuildDataSource; QueryBuildRange queryBuildRange; QueryRun queryRun; Qty total; InventTrans inventTrans; ; query = new Query(); queryBuildDataSource = query.addDataSource(tableNum(InventTrans)); queryBuildDataSource.addSelectionField(fieldNum(InventTrans,Qty),SelectionField::Sum); queryBuildDataSource.orderMode(OrderMode::GroupBy); queryBuildRange = queryBuildDataSource.addRange(fieldNum(InventTrans,ItemId)); queryBuildDataSource = queryBuildDataSource.a...
Read more

"Like" with "AND" Operator through Query in Dynamics Ax

static void RAJ_AndLikeOperator(Args _args) Query query = new Query(); QueryRun queryRun; InventTable inventTable; ; query.addDataSource(tableNum(InventTable)).addRange(fieldNum(InventTable, ItemId)).value("100*, 110*, 120*"); queryRun = new QueryRun(query); while(queryRun.next()) inventTable = queryRun.get(tableNum(InventTable)); info(inventTable.ItemId); } }
Read more

query range example

queryBuild     = this.query(); confirmedDlv = queryBuild.dataSourceTable(tablenum(PurchLine)).addRange(fieldnum(PurchLine, ConfirmedDlv)); confirmedDlv.value(queryRangeConcat(strfmt('..%1', queryValue(collectionLetterDate-1)), *queryNotValue(dateNull())*)); queryRun_PurchCollectionLetterList            = new QueryRun(queryBuild); //PUT BREAKPOINT HERE
Read more

Using AOT Query Object in X++ Code

use the AOT Query Object in our X++ code. Steps: 1) Create a AOT Query Object as your requirement. 2) Create a Job and paste the below code to check the Query Object. static void ExecuteAOTQuery(Args _args) { QueryRun queryRun; Counter totalRecords; ; queryRun = new QueryRun(queryStr( CreatedAOTQueryName )); if (queryRun.prompt()) { while (queryRun.next()) { totalRecords++; } } info(strFmt(“Total Records : %1”, totalRecords)); }
Read more