HI,
I have query , which has an outer join . and the outer join table has a filter condition. if i remove the filter , then the query returns a different result set, if i put the filter condition then it does different , though the filter value may not exist in the table . To clarify my point here is a sample table with result set and the queries.
SELECT x.y, x.b FROM x
records from table X
Y B
-------- -----------------------
1 2007-05-04 11:49:45.86
11111 2007-05-04 11:49:45.86
2 2007-05-24 08:31:47.96
3 2007-05-24 08:31:55.54
4 2007-05-24 08:32:03.67
5 2007-05-24 09:02:04.13
6 2007-05-24 09:02:08.85
6 2007-07-27 15:42:17.603
6 2007-07-28 15:42:45.366
SELECT z.y, z.b FROM z
records from table Z
Y B
---- ----
0 record(s) selected
query with the outer join
SELECT X.y, x.b , Z.y, z.b FROM X, Z WHERE X.Y = Z.Y (+)
Y B Y B
-------- ----------------------- ------ ------
5 2007-05-24 09:02:04.13 (null) (null)
11111 2007-05-04 11:49:45.86 (null) (null)
1 2007-05-04 11:49:45.86 (null) (null)
6 2007-07-28 15:42:45.366 (null) (null)
6 2007-07-27 15:42:17.603 (null) (null)
6 2007-05-24 09:02:08.85 (null) (null)
4 2007-05-24 08:32:03.67 (null) (null)
2 2007-05-24 08:31:47.96 (null) (null)
3 2007-05-24 08:31:55.54 (null) (null)
query with outer join with filter
SELECT X.y, x.b , Z.y, z.b FROM X, Z WHERE X.Y = Z.Y (+) AND Z.CX = 4
Y B Y B
---- ---- ---- ----
0 record(s) selected
Although I was expecting the last query to return all the records from table X. Am I doing something wrong ? I need to have that filter criteria on table z, but i want the query to return the result set as well as in the third query. All helps appreciated.
thanks