Openedge 11.7 returns different result for COUNT(DISTINCT <ColumnName>) when compared to Oracle/MSSQL


New Member
Hi everyone,

I am facing a problem that has left me a little stumped. Any help or insight will be very helpful.

A table called Staples in TestV1 schema was loaded in Oracle, MSSQL and Openedge. There is a customer bug stating that a SQL query to extract a table's data in Openedge 11.1 is different to what is returned by Oracle and MSSQL databases. I now copied the table from Oracle and loaded it in Openedge.

The following queries yield the exact same result set in both Oracle and Openedge:
SELECT "Order ID" FROM "TestV1"."Staples" "Staples" order by "Order ID";
SELECT distinct("Order ID") FROM "TestV1"."Staples" "Staples" order by "Order ID";
SELECT "Order ID", count(*) FROM "TestV1"."Staples" "Staples" group by "Order ID" order by "Order ID";
SELECT "Staples"."Order ID", COUNT("Staples"."Order ID") AS "ctd_Order_ID_ok" FROM "TestV1"."Staples" "Staples" GROUP BY "Staples"."Order ID" order by 1;

But, I see that the result is not the same for this query:
In Oracle:
SELECT COUNT(DISTINCT "Staples"."Order ID") AS "ctd_Order_ID_ok" FROM "TestV1"."Staples" "Staples" HAVING (COUNT(1) > 0);
OUTPUT - 13653

(MSSQL also returns 13653)

In Openedge:
SELECT COUNT(DISTINCT "Staples"."Order ID") AS "ctd_Order_ID_ok" FROM "TestV1"."Staples" "Staples" HAVING (COUNT(1) > 0);
OUTPUT - 14256

This is my Openedge environment:
DB Version - 11.7
OS Version - Windows Server 2016
Table definition in Oracle (Oracle-Staples-DDL.txt) and Openedge (Openedge-Staples-DDL.txt) attached. No index/Contraints/Triggers/Dependencies on the table in both.

Data file (extract from Oracle) used to load Openedge table seems to be too large to attach here.

Am I missing to notice something here Or is this a bug?



  • Openedge-Staples-DDL.txt
    2.2 KB · Views: 5
  • Oracle-Staples-DDL.txt
    1.9 KB · Views: 3
Last edited: