Answered

Building and using QueryRun in X++

(0) Share

Report

Posted on by Community Member

I have a need to be able to dynamically build a query in X++. I can do so and make it work. My question is:

Is there any way, using the Query classes, to get the entire result set in 1 buffer. I understand the:

While(queryRun.Next())
{
    common = queryRun.get(tableName2Id('InventTable'));
}

but this just does it record by record. I am wanting the entire result table at once. Any ideas? I have been searching for a long time.

Thanks

*This post is locked for comments

I have the same question (0)

All responses (16)

Answers (1)

André Arnaud de Cal... 299,071 Super User 2025 Season 2 on at

Like (0)

Report

RE: Building and using QueryRun in X++

Hi,

what do you want to do with the entire result?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Building and using QueryRun in X++

I would like to be able to build an in memory table using its results. The problem I am running into is that when you .insert() into an inmemory table the recId gets changed. I need a table buffer with all of the records that have the original recIds. If there is another way to do this I am all ears as I have been stuck on this for a bit.

Thanks.

Was this reply helpful? Yes No
André Arnaud de Cal... 299,071 Super User 2025 Season 2 on at

Like (0)

Report

RE: Building and using QueryRun in X++

Hi,

You can consider creating a temporary table which also contains an additional field for the original record ID. You can then loop the queryrun, fill the fields of the in memory table and also the original record ID. Then the recId of the temporary table is not important.

Will this work for you?

Was this reply helpful? Yes No
Joris dG 17,775 on at

Like (0)

Report

RE: Building and using QueryRun in X++

This is an interesting question. I guess the answer is, no there is no way. However, I'm not sure what you would want it for, considering a list in itself is pointless, at some point you always want to iterate it to do something with that list. There are ways to iterate the query and store the records in a list...

So what is your scenario? There must be a specific reason you ask this.

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

RE: Building and using QueryRun in X++

No, you have to traverse the query results at least once. From there you can do whatever with the results (like store them in a temp table). Also, look at recordsets for bulk inserts or updates.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Building and using QueryRun in X++

Well I am doing this in memory table as a common type because we will be doing this with many tables. I would doubt there is a way to dynamically add a field to an in memory table. I had thought of this at one point.

I just want to have a table buffer that contains all of the records returned from a dynamic query including their recIds. I am surprised that there is nothing that can do this since both the query service and the business connector allow this from Visual Studio. You can also do it from a static query inside of ax using a select statement. Unfortunately you can not dynamically make a select statement from inside ax.

Was this reply helpful? Yes No
André Arnaud de Cal... 299,071 Super User 2025 Season 2 on at

Like (0)

Report

RE: Building and using QueryRun in X++

Hi,

[quote user="brilliancenp"]

Well I am doing this in memory table as a common type because we will be doing this with many tables.[/quote]

What are you trying to achieve and in which way? Microsoft Dynamics AX has a lot of coding and apparently they managed to create an application which works. Probably you are inventing something new?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Building and using QueryRun in X++

Yes I am. I want to be able to be able to create a result set from parameters that I am feeding through a custom ax service. I am more or less just toying around with some ideas but I want to be able to create a common table buffer with all of the records I need for the table that I specify so I can pass it to other classes.

And while I agree that AX does work, and works well, it surprises me that the Query Run class, while it obviously could access the entire data set at once, only allows for record by record traversal. You learn something new everyday I suppose :)

Was this reply helpful? Yes No
André Arnaud de Cal... 299,071 Super User 2025 Season 2 on at

Like (0)

Report

RE: Building and using QueryRun in X++

I understand this, but what would be the purpose of the original record id? You have not mentioned that part in your latest answer.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Building and using QueryRun in X++

Oh yes I apologize. The need for these to be in memory tables is not there. At the time of the original post I figured they may meet my needs but I was just experimenting. I had never used in memory tables before and thought since they don't affect the original table, I may be able to insert without changing the recIds.

I need the original recIds to perform business logic on them using ax classes just as you would for a table.find() method passing it a table record/buffer. I really just need the original table records. My want is this:

Building a dynamic query inside of ax using parameters that I feed through a service from an outside program and the creation of a table buffer containing all of those records.

It doesn't sound like this is a possibility so I will just have to redesign what I am doing.

Was this reply helpful? Yes No