Get query range from x++???

Finance and Operations/AX Technical Forum
1

Hi,

If I give range for AccountNum field as 1001…1201 (double period filter) in custTable Query, How to write job to get all the AccountNum values from 1001 to 1201…

Please give me Idea, I will proceed.

Very Thanks,

Nunn@

2

Use QueryRun class to execute the query:

Query q = ...
QueryRun qr = new QueryRun(q);
CustTable ct;

while (qr.next())
{
    ct = qr.get(tableNum(CustTable));
    info(ct.AccountNum);
}
3

Thanks Martin,

Is there any other way to get values, Because, In my scenario I already used queryRun.next() in another place for another range, So I can’t use that.

Regards,

Nunn@

4

Just look into my code once,

private void getPostingProfile(QueryRun _queryRun)

{

Query query;

QueryBuildDataSource qbd;

QueryBuildRange queryRange;

int cnt, i,j,;

DictTable dictTable;

DictField dictField;

Str range1;

query = _queryRun.query();

for(i = 1; i <= query.dataSourceCount(); i++)

{

if (query.dataSourceNo(i).table() == tableNum(CustTrans))

{

cnt = query.dataSourceNo(i).rangeCount();

dictTable = new DictTable(query.dataSourceNo(i).table());

for (j = 1; j <= cnt; j++)

{

queryRange = _queryRun.query().dataSourceNo(i).range(j);

dictField = new DictField(query.dataSourceNo(i).table(), fieldname2id( query.dataSourceNo(i).table(), queryRange.AOTname()));

if (queryRange.value() && Fieldname2id( query.dataSourceNo(i).table(), queryRange.AOTname()) == fieldNum(CustTrans, PostingProfile))

{

range1 = queryRange.value(); //Here I get range as a string,(EG:- 1001…1201) But I want all values.

}

}

}

}

5

Query Q = new Query();

QueryBuildDatasource Qbds;

QueryBuildRange Qbr;

;

Qbds = Q.addDataSource(tableNum(CustTable));

Qbr = Qbds.addRange(fieldNum(CustTable, AccountNum));

Qbr.value(SysQuery::range(‘1001’, ‘1201’));

6

You simply can’t get results of a database query without running it (QueryRun.next()). You have two options:

  1. You say you already runs the query, therefore you can extend the existing code to get AccountNum too.
  2. Create a new QueryRun instance for fetching AccountNums. You can use the same query (Query class), or you can modify it (e.g. you may want to return no field but AccountNum).
7

Thanks Martin,

Yes I tried your second option. Please read my another reply (My code),

if (queryRange.value() && Fieldname2id( query.dataSourceNo(i).table(), queryRange.AOTname()) == fieldNum(CustTrans, PostingProfile))

{

range1 = queryRange.value(); //Here I get range as a string,(EG:- 1001…1201) But I want all values.(Especially Double Period filter)

}

8

My second option is executing the query by a separate QueryRun instance - you don’t do anything like that in your code! Your code tries to read a range value from a query definition in memory; it never sends the query to database. I already gave you the code to use in my first answer.

You don’t need the code you showed at all, but for completeness: you can find a range by QueryBuildDataSource.findRange(), e.g. qbd.findRange(fieldNum(CustTrans, PostingProfile)).

9

Hi Martin,
I found your answers very helpful.
I was wondering if I had another filter that is the date range along with the accountNum, how will I incorporate two ranges. Moreover also I have more than one table as a datasource.How will I get it to work?
I am new to ax, your help is appreciated.

10

Dhruv, you’ll execute the query in exactly the same way, even if you add ten datasources and fifty ranges. Maybe you’re trying to do something else than what’s discussed in this thread, aren’t you? If it’s the case, please use the thread you’ve created to explain your actual scenario. It’s not clear in the moment.