Microsoft Dynamics AX (Archived)

How to apply 2 ranges in AOT query for the same table?

(0) Share

Report

Posted on by Community Member

I have one table , it has 4 fields called : Code, CostCenter, Enum, Modifieddatetime

Data is : Code CostCenter Enum Modifieddatetime

ABC 123 0 Jan

ABC 123 2 Feb

Here enum : 0 is available, 2 is restricted, First I want to apply the range for modifieddatetime latest record, then I want to apply range for enum 2, If it is enum 2 , I should not bring that record. In this case I should not bring both records. Because the first record modified date time lessthan 2nd record. The same rule apply for Enum : 1 and 2 also. Please give any suggestions on this. Thanks in advane

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (0)

Sort by

nino232323 362 on at

Like (0)

Report
Copy link

Link copied!

Hi,

you could try with this query if i understood your question:

Table table1, table2;

select firstonly table1

order by table1.ModifiedDateTime

exists join firstonly table2

order by table2.ModifiedDateTime

where table1.recid == table2.recid

&& table1.enum != 2;

Nino

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Hi Nino, Thanks for your response, the above query not worked. I need to check like , for the same code and same costcenter, filter recent record, then filter enum 2. For suppose , first record has modifieddatetime march. It should pick that. if it is less, it should not. Hope you understood my question.

Was this reply helpful? Yes No
André Arnaud de Cal... 304,323 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!
Hi Vijaya,

Do you expect a lot of records to be processed in this way? What is the exact business scenario here? You can use multiple queries/statements to get your result by comparing the outcome of these records.

Eg.

select tableAvailable where enum == 0 order by Modifieddatetime desc select tableRestricted where enum == 2 order by Modifieddatetime desc if (tableAvailable.Modifieddatetime > tableRestricted.Modifieddatetim) { …. }

This is not the best solution for processes which needs to perform very fast. So, can you explain the purpose?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Hi André Arnaud de Calavon , Thanks for your response. Yes. In the exact business scenario, can expect lot of records. may be 50Lak records in my scenario. I have tried above suggested one, but its not happening with in less time. It has taken more than a day, finally I stopped it and trying another way.

Scenario : I need to export the data from multiple tables. In those one table is above one. This table contains bunch of records, it contains multiple records with the same code & costcenter with different enum & different modified date time. So filter should happen on both fields modifieddatetime and enum if it has same code and costcenter. First modifieddatetime then enum.

Was this reply helpful? Yes No
André Arnaud de Cal... 304,323 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hi Vijaya,

So, my expectation that performance was important is correct. Possibly during inserts and updates or using another batch job, you can maintain a field on this table or another table to prepare data, so a single call can be used when exporting the records.

Was this reply helpful? Yes No