-
I have master table location and detail table
application.
select l.location_number, count(a.application_id)
from application a, location l
where
l.location_id = 102884
and l.location_id = a.location_id(+)
group by l.location_number
/
LOCATION_NUMBER COUNT(A.APPLICATION_ID)
------------------------------ -----------------------
2323 0
While if I add a condition in where clause:
and a.posted >= to_date('27-OCT-2000 00:00:00', 'DD-MON-YYYY HH24:MI:SS')
and a.posted <= to_date('22-NOV-2000 00:00:00', 'DD-MON-YYYY HH24:MI:SS')
The query will be:
select l.location_number, count(a.application_id)
from application a, location l
where
l.location_id = 102884
and l.location_id = a.location_id(+)
and a.posted >= to_date('27-OCT-2000 00:00:00', 'DD-MON-YYYY HH24:MI:SS')
and a.posted <= to_date('22-NOV-2000 00:00:00', 'DD-MON-YYYY HH24:MI:SS')
group by l.location_number
/
I got:
no rows selected
How can this happen? I want to see:
LOCATION_NUMBER COUNT(A.APPLICATION_ID)
------------------------------ -----------------------
2323 0
Note: This is just partial query which I abstract
to ask help.
Also, posted field is date field.
Thanks,
-
Cleaning up the query a little, we might notice...
SELECT
L.LOCATION_NUMBER,
COUNT(*)
FROM
APPLICATION A,
LOCATION L
WHERE
L.LOCATION_ID = 102884 AND
A.LOCATION_ID (+)= L.LOCATION_ID AND
A.POSTED >= TO_DATE('27-OCT-2000', 'DD-MON-YYYY') AND
A.POSTED <= TO_DATE('22-NOV-2000', 'DD-MON-YYYY')
GROUP BY
L.LOCATION_NUMBER
that while you start by outer-joining table A into the query, you then eliminate the outer-join by using straight comparisons against the POSTED field. You would need to do (+)>=.
In Oracle, if a table is outer-joined anywhere, the outer-joined restrictictions will be performed first, then the outer-joins, then any remaining, non-outer restrictions. This is different than SQLServer. In SQLServer, this would have worked, but not in Oracle.
Hope this helps
- Chris
-
Thanks!
It is the problem!
