Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Inherited Table EcoRes...
Microsoft Dynamics AX (Archived)

Inherited Table EcoResDistinctProduct contains more data than expected

(0) ShareShare
ReportReport
Posted on by RockyLee 35

From what I know, table EcoResProduct is a base table, table EcoResProductMaster (TableId = 3267) contains products which are product masters, EcoResDistinctProductVariant (TableID=3266) contains product variants, and EcoResDistinctProduct (TableId=3265) contains products which are not product masters and product variants.

So, I tried to use the following code (SQL - sent to SQL server, as well as X++) to check:

1. EcoResDistinctProduct:

SQL:

SELECT *  --> 9643 rows
FROM EcoResProduct
WHERE InstanceRelationType = 3265

X++:

int recCount = 0;

while select * from EcoResDistinctProduct

{

    recCount ++; //-->12570 rows

}

-----------

2. EcoResProductMaster:

SQL:

SELECT *  //--> 424 rows
FROM EcoResProduct
WHERE InstanceRelationType = 3267

X++:

int recCount = 0;

while select * from EcoResProductMaster

{

    recCount ++; //-->424 rows

}

---------------

3. EcoResDistinctProductVariant:

SQL:

SELECT *  //-->2927 rows
FROM EcoResProduct
WHERE InstanceRelationType = 3266

X++:

int recCount = 0;

while select * from EcoResDistinctProductVariant

{

    recCount ++; // --> 2927 rows

}

I compared the number of records from 2 commands for each case above and found out that the case #1 has an unexpected result.

The numbers of records in case #1 are different: the X++ statement (run on AOS) returns more records than SQL statement (sent directly to SQL server).

I am not sure why, please give me some hint.

Thanks,

Rocky

*This post is locked for comments

  • Verified answer
    RockyLee Profile Picture
    RockyLee 35 on at
    RE: Inherited Table EcoResDistinctProduct contains more data than expected

    The issue with case #1:  the X++ select statement ...select * from EcoResDistinctProduct... returns more data than expected (12570 rows instead of 9643 rows).

    I use this code to investigate a bit further:

    while
    select InstanceRelationType, count(RecId)
    from ecoResDistinctProduct
    group by ecoResDistinctProduct.InstanceRelationType
    {
        info(strFmt("InstanceRelationType=%1 - RowsCount=%2",

                           ecoResDistinctProduct.InstanceRelationType,

                           ecoResDistinctProduct.RecId));
    }

    InfoLog shows the result messages:

    InstanceRelationType=3265 - RowsCount=9643
    InstanceRelationType=3266 - RowsCount=2927

    So, the X++ select statement of the case #1, also includes data from table EcoResDistinctProductVariant. This is what I don't expect. Not sure this issue is a AX 2012's bug or something in the table setting was setup wrongly.

    To work around this issue, I have to use the following code:

    select *

    from ecoResDistinctProduct

    where ecoResDistinctProduct.InstanceRelationType == 3265

  • RockyLee Profile Picture
    RockyLee 35 on at
    RE: Table EcoResDistinctProduct contains more data than expected

    From the test result, it looks line X++ table EcoResDistinctProduct also contains data from X++ table EcoResDistinctProductVariant.

  • RockyLee Profile Picture
    RockyLee 35 on at
    RE: Table EcoResDistinctProduct contains more data than expected

    In which case #, you think that the SQL condition is wrong,
    André Arnaud de Calavon?

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 294,722 Super User 2025 Season 1 on at
    RE: Table EcoResDistinctProduct contains more data than expected

    Hi Rocky,

    Actually, you can use the next statement to do a count using x++ which is much faster:

    EcoResDistinctProduct distinctProduct
int recCount;

select count(RecId) from distinctProduct

recCount = distinctProduct.RecId;


    If you have a difference, you can actually export the data from SQL and also the EcoResDistinctProduct table from the table viewer in AX and compare them to find out what is actually different.

  • Suggested answer
    nmaenpaa Profile Picture
    nmaenpaa 101,158 Moderator on at
    RE: Table EcoResDistinctProduct contains more data than expected

    Your SQL select statement has different condition (InstanceRelationType) than your x++ statement.

    Also please note that you can get count of records without iterating through them:

    int recCount = 0;

select count(RecId)* from EcoResProductMaster;
recCount = EcoResProductMaster.RecId;


Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

🌸 Community Spring Festival 2025 Challenge Winners! 🌸

Congratulations to all our community participants!

Adis Hodzic – Community Spotlight

We are honored to recognize Adis Hodzic as our May 2025 Community…

Kudos to the April Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard > Microsoft Dynamics AX (Archived)

#1
Mohamed Amine Mahmoudi Profile Picture

Mohamed Amine Mahmoudi 100 Super User 2025 Season 1

#2
Community Member Profile Picture

Community Member 48

#3
shanawaz davood basha Profile Picture

shanawaz davood basha 6

Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans