Re: Plase help: outer join referencing multiple columns

T. Schiller wrote:
>
> [snip]
>
> The offending statement runs like this:
>
> select t1.col1, t1.col2, t1.col3, t2.col1 from table1 t1, table2 t2
> where (( t1.colx (+) = t2.colu ) and ( t1.coly (+) = t2.colv ) and (
> t1.colz (+) = t2.colw ))
> and ( t1.time1 between '01-JAN-96' and '31-JAN-96' )
>
> [snip]
>
> What I would expect as a result of this query are 20 rows with 10 of
> them having a null
> in t2.col1.
>
> [snip]

If what you want is all records from t1 that meet the time restriction, and the matching records (if any) from t2, then you have the outer join going the wrong way.

What you want your SQL statement to look like is:

SELECT	t1.col1, t1.col2, t1.col3, t2.col1
FROM	table1 t1, table2 t2
WHERE	(t1.colx = t2.colx (+)) and
	(t1.coly = t2.coly (+)) and
	(t1.colz = t2.colz (+)) and
	(t1.time1 between '01-JAN-96' and '31-JAN-96');

If t1.time1 contains time as well as date information, you might run into another problem. t1 records for '31-JAN-96' that also have a time portion won't show up with this query (since '31-JAN-96' really means Midnight Jan 31, 1996). If this is the case, you probably want to change the last line of your statement to:

        (t1.time1 >= '01-JAN-96' and t1.time1 < '01-FEB-96'); You could also use (trunc(t1.time1) between '01-JAN-96' and '31-JAN-96'); but that would prevent oracle from using the index you have on t1.time1

-- 
-------------------------------------------------
Ken Johnson -  Senior Technical Consultant
Tapestry Computing, Inc. http://www.tapestry.com