MINUS problem

From: Phil R Lawrence <prl2_at_lehigh.edu>
Date: 1998/03/12
Message-ID: <6e9ng5$16rm@fidoii.cc.Lehigh.EDU>#1/1

Following are two count queries and their results. I want to isolate the records that appear in the count of the 1st query but not in the 2nd. To that end, I wrote the MINUS query which is also below.

PROBLEM: The MINUS query returns the same number of rows as the 1st query counts. Why? How can I fix this?

TIA, 1st query and result:

1 select count(*) from (
2 select distinct

  3           gurmail_pidm,
  4           gurmail_term_code,
  5           gurmail_letr_code

6 from general.gurmail
7 where gurmail_date_printed IS NULL 8* )

COUNT(*)

4363

2nd query and result:

1 select count(*) from (
2 select distinct

  3           gurmail_pidm,
  4           NULL,                                !!!! HERE'S THE
DIFFERENCE !!!
  5           gurmail_letr_code

6 from general.gurmail
7 where gurmail_date_printed IS NULL 8* )

COUNT(*)

4357

MINUS query that returns 4363 rows:

1 (select distinct

  2         gurmail_pidm,
  3         gurmail_term_code,
  4         gurmail_letr_code,
  5         gurmail_date_printed

  6 from general.gurmail
  7 where gurmail_date_printed is null)   8 minus
  9 (select distinct

 10         gurmail_pidm,
 11         NULL,
 12         gurmail_letr_code,
 13         gurmail_date_printed

14 from general.gurmail
15* where gurmail_date_printed is null)

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Phil R Lawrence               phone: 610-758-3051
Programmer / Analyst      e-mail: prl2_at_lehigh.edu
194 Lehigh University Computing Center
E.W. Fairchild - Martindale, Bldg. 8B
Bethlehem, PA  18018
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Received on Thu Mar 12 1998 - 00:00:00 CST