Consultor Eletrônico

Status: Unverified

SYMPTOM(s):

Similar SQL queries give different results

SELECT DISTINCT gives more records for one query than another

SQL statement gives duplicate records:

SELECT "ord-main", "ord-store"
FROM "pub"."sohdr"
WHERE "ord-main" in (
SELECT DISTINCT "ord-main"
FROM "pub"."sohdr"
WHERE ("mod_date" = (SELECT "lastrundate" FROM "pub"."biscriteria")))

.

SQL statement gives the correct number of records:

SELECT "ord-main", "ord-store"
FROM "pub"."sohdr"
WHERE "ord-main" in (
SELECT distinct "ord-main"
FROM "pub"."sohdr"
WHERE ("mod_date" = 'DD/MM/YYYY' ))

mod_date is the date of the lastrundate in the first query.

CAUSE:

Bug# 20040526-024

FIX:

1. Upgrade to 10.0B
2. Upgrade to 9.1E
3. As a workaround, place the DISTINCT keyword after the first SELECT keyword.