Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL - Select and filtering whereby a date is not null.
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