Details
Major Description
COUNT(DISTINCT col) returns wrong results
SELECT distinct L.AUDIENCEKEY
FROM unica.BM_LUCKYDRAW L, unica.BM_FULFILLMENT F
WHERE F.TREATMENTCODE = L.TREATMENTCODE
AND F.AUDIENCELEVEL = L.AUDIENCELEVEL
AND F.AUDIENCEKEY = L.AUDIENCEKEY
AND L.CAMPAIGNCODE = 'C000000301'
AND CDR_TYPE = 1
AND F.PROVISIONPARAM4 = '100'
AND TO_DATE(L.INSERT_DATE) = cast('2016-09-09' as timestamp) ;
Return rows 36556 rows see in attach file.
--------------------------------------------------------------------------------------
but count distinct from below query
SELECT count( distinct L.AUDIENCEKEY)
FROM unica.BM_LUCKYDRAW L, unica.BM_FULFILLMENT F
WHERE F.TREATMENTCODE = L.TREATMENTCODE
AND F.AUDIENCELEVEL = L.AUDIENCELEVEL
AND F.AUDIENCEKEY = L.AUDIENCEKEY
AND L.CAMPAIGNCODE = 'C000000301'
AND CDR_TYPE = 1
AND F.PROVISIONPARAM4 = '100'
AND TO_DATE(L.INSERT_DATE) = cast('2016-09-09' as timestamp) ;
Return 36673 rows