weird SQL result

[sysdba]

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,

[chrisrlong]

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

[sysdba]

Thanks!

It is the problem!