Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL - Select and filtering whereby a date is not null.

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

From: <ken_at_kendenny.com>
Date: 29 Mar 2005 10:36:33 -0800
Message-ID: <1112121393.675865.89280@l41g2000cwc.googlegroups.com>


Kal wrote:
> 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

The problem with the query in your first post is that if there were two rows in Oss_Relationships with the same Rel_Source_Per_Gro_Id, both with Rel_Rty_Code='CTE' and one had a null Rel_End_Date and the other's Rel_End_Date was not null, the first query would find that one but the second query wouldn't. You could possible improve performance slightly by changing your query to

 SELECT P.Per_Id, P.Per_First_Names, P.Per_Surname  FROM Oss_Persons P, Oss_Relationships R

 WHERE P.Per_Id = R.Rel_Source_Per_Gro_Id
   AND R.Rel_Rty_Code = 'CTE'
   AND P.Per_Id NOT IN
      (
 	SELECT U.Rel_Source_Per_Gro_Id
 	  FROM Oss_Relationships U
 	  WHERE U.Rel_Rty_Code = 'CTE' AND U.Rel_End_Date IS NULL
      );

I'm not sure which of these will give you better performance. You could do an explain plan on both to see what the difference was.

Ken Denny Received on Tue Mar 29 2005 - 12:36:33 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US