Iterating through a container on a while select statement

(0) Share

Report

Posted on by greengrimms

1,400

Hi guys, I have the following statement in AX:

conCount = 1;

while select forupdate table1

where table1.AccountNum == conPeek(accountNumContainer, conCount)

{

table1.Status = "Completed";

table1.update();

if(conCount <= conLen(accountNumContainer)

conCount++;

}

The problem that I'm having with this is that the while loop iterates through the first element in the container (which has 4 elements in this case), updates the first record but then continues the execution out of the while select statement... Am I missing something obvious? Why is it not iterating through the whole container? Does AX support this functionality?

Any help on this issue will be greatly appreciated.

Thanks!

*This post is locked for comments

I have the same question (0)

All responses (4)

Answers (3)

Verified answer

André Arnaud de Cal... 301,130 Super User 2025 Season 2 on at

Like (1)

Report

Hi Danilo,

The problem is that it does a select statement where at that time only one value is used: 'the conCount > 1'. So it retrieves one record and does a loop on the single record.

It would be better to use a statement like:

while conCount <= conLen(accountNumContainer)

{

select firstonly forupdate table1 where ....

table1.Status = "Completed";

table1.Update();

conCount++;

}

Was this reply helpful? Yes No
Verified answer

Vilmos Kintera 46,149 on at

Like (1)

Report
int i; for (i = 1; i <= conLen(yourContainer); i++) { while select yourtable where yourtable.yourfield == conPeek(yourContainer, i) { doSomething(); } }

You should be looping through the container elements, there are various ways to do that with forward and backward-testing the position within the container.

Was this reply helpful? Yes No
Verified answer

Martin Dráb 237,967 Most Valuable Professional on at

Like (2)

Report
Other guys already gave you some solutions, but let me explore a few other ideas.

The flaw in your thoughts was that you you expected the condition (where table1.AccountNum == ...) to be evaluated several times, but it's not the case and it wouldn't make sense. You construct a query, send it to database (once!) and then iterate the records returned from database (which doesn't care about the query definition).

Therefore your code is equivalent to this, which makes the logic much more obvious.

conCount = 1; string firstAccount = conPeek(accountNumContainer, conCount); while select forupdate table1 where table1.AccountNum == firstAccount; { table1.Status = "Completed"; table1.update(); ... }

A problem with solutions above is that both run several selects statements against database. If you want to avoid it, you can construct a single query with a range for each value in the container. Like this:

Query query = new Query(); QueryBuildDataSource ds = query.addDataSource(tableNum(Table1)); for (i = 1; i <= conLen(yourContainer); i++) { ds.addRange(fieldNum(Table1, AccountNum)).value(queryValue(conPeek(yourContainer, i))); }

Then you can run the whole thing at once.

Alternatively you may be able to use update_recordset to update the Status without transferring all the data back and forth.

Also, if you have a huge amount of such values, you may want to put them to a TempDB table instead of a container and then use the table in a join.

There are many options; which one you should choose depends on your particular situation.

Was this reply helpful? Yes No
greengrimms 1,400 on at

Like (1)

Report

Hi André, Vilmos and Martin. Thank you very much for all your answers. I took the approach of using a for loop and then doing the while select inside of it. Thank you so much for taking the time to write the answers, I could take information out of every one of them. They were really helpful.

Was this reply helpful? Yes No