Re: MINUS problem

On Thu, 12 Mar 1998 17:27:17 -0500, "Phil R Lawrence" <prl2_at_lehigh.edu> wrote: Hi,

your query will always return all the rows from query1 if there aren't rows in gurmail with gurmail_term_code = NULL..

What you realy want may be:

  select count(*) from (

   	select distinct
           		gurmail_pidm,
           		gurmail_term_code,
           		gurmail_letr_code
    	from   
		general.gurmail
    	where  
		gurmail_date_printed IS NULL
	AND	
		gurmail_term_code != 0)

>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
>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
>
>

--

Regards

Matthias Gresz    :-)

GreMa_at_T-online.de