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
