Hi All,
I'm trying to get the count of distinct quality order IDs in InventNonConformanceOrigin, where the quality order ID also exists in the InventQualityOrderTable:
select count(RecId) from inventNonConformanceOrigin
group by inventNonConformanceOrigin.InventQualityOrderId
join inventQualityOrderTable
where inventNonConformanceOrigin.InventQualityOrderId == inventQualityOrderTable.QualityOrderId;
The SQL query for it is:
SELECT COUNT(DISTINCT INVENTQUALITYORDERID) FROM INVENTNONCONFORMANCEORIGIN
INNER JOIN INVENTQUALITYORDERTABLE ON INVENTNONCONFORMANCEORIGIN.INVENTQUALITYORDERID = INVENTQUALITYORDERTABLE.QUALITYORDERID
The issue I'm have is that this just returns a value of 1 in inventNonConformanceOrigin.RecId. It should be returning over a thousand.
I have a similar while select query that selects distinct records using group by without any issues. It pulls all the records as expected:
while select InventQualityOrderId from inventNonConformanceOrigin
group by inventNonConformanceOrigin.InventQualityOrderId
join inventQualityOrderTable
where inventNonConformanceOrigin.InventQualityOrderId == inventQualityOrderTable.QualityOrderId
{
//
}
Does anyone know what I'm missing with using the group by, or is there an alternate way to get the count I'm after? I've searched around and looked in this forum, but I wasn't able to find an existing post about this scenario. Any help is greatly appreciated, thank you!
