Re: SQL - Select and filtering whereby a date is not null.

From: Kal <kalgill_at_talk21.com>
Date: 27 Mar 2005 07:33:24 -0800
Message-ID: <ec0f1d86.0503270733.65f2df7a@posting.google.com>

Hi All,

Thanks for your various responses, I think this problem may have been down to my relative inexperience in using Oracle and SQL as I am beginner in both but see the select below which seems to have solved the problem and returns the correct results:

SELECT P.Per_Id, P.Per_First_Names, P.Per_Surname FROM Oss_Persons P
WHERE P.Per_Id IN

	  	  (
		  SELECT U.Rel_Source_Per_Gro_Id
		  FROM Oss_Relationships U
		  WHERE U.Rel_Rty_Code = 'CTE' AND U.Rel_End_Date IS NOT NULL
		  MINUS
		  SELECT U.Rel_Source_Per_Gro_Id
		  FROM Oss_Relationships U
		  WHERE U.Rel_Rty_Code = 'CTE' AND U.Rel_End_Date IS NULL  
		  );

There may be a better way of doing this, but this is working effectively and seems to be producing the correct results, all further suggesttions are welcome.

Regards

Kal

"Ed Prochak" <ed.prochak_at_magicinterface.com> wrote in message news:<1111600589.349998.228840_at_l41g2000cwc.googlegroups.com>...
> Show us your workaround. It might have a clue in what's wrong.
>
> FYI, I do not see S.Rel_End_Date in your query. There is no table alias
> or table named "S" in the query you posted. How do you know it is
> coming back with NULL?
>
> Are you sure you are not missing some other join criteria?
>
> What happens if you change Rel_End_Date IS NOT NULL;
> to Rel_End_Date >to_date('1900-01-01','YYYY-MM-DD') ;
> ?
>
> Ed.
Received on Sun Mar 27 2005 - 09:33:24 CST