-
Steve,
select * from application a
where a.posted >= to_date('27-OCT-2000','dd-mon-yyyy') and a.posted <= to_date('22-NOV-2000','dd-mon-yyyy');
will return a lot records.
SQL> r
1 select l.location_number, count(a.application_id)
2 from application a, location l
3 where
4 l.location_id = 102884
5 and l.location_id = a.location_id(+)
6 and a.posted >= to_date('27-OCT-2000','dd-mon-yyyy')
7 and a.posted <= to_date('22-NOV-2000','dd-mon-yyyy')
8* group by l.location_number
no rows selected
SQL>
For who comes to see the orginal question:
SQL> r
1 select l.location_number, count(a.application_id)
2 from application a, location l
3 where
4 l.location_id = 102884
5 and l.location_id = a.location_id(+)
6* group by l.location_number
LOCATION_NUMBER COUNT(A.APPLICATION_ID)
------------------------------ -----------------------
2323 0
I want to see
LOCATION_NUMBER COUNT(A.APPLICATION_ID)
------------------------------ -----------------------
2323 0
for the following query too!
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','dd-mon-yyyy')
and a.posted <= to_date('22-NOV-2000','dd-mon-yyyy')
group by l.location_number
/
-
Actuall my understanding is that it would work if you typed in this:
and to_char((a.posted), 'DD-MON-YYYY') >= 27-OCT-2000
and to_char((a.posted), 'DD-MON-YYYY') <='22-NOV-2000
You might have to play around with the brackets, trunc as well etc.
My understanding (and from previous usage) is that we always convert our table's field values (at least for display) before making comparisons.
you can use to_date, but I have always had luck with to_char.
Good luck and I hope it helps.
Nirasha
-
Are you absoletly sure? Did you tried it, cus if you did not tried it, you dont have the proof that the select:
select * from application a
where a.posted >= to_date('27-OCT-2000','dd-mon-yyyy') and a.posted <= to_date('22-NOV-2000','dd-mon-yyyy');
Does actually Works.
Do a select count(*) if you think lots of record will be return.
But you need the proof that the where on the date works, cus that is the problem.
if you do
1 select l.location_number, count(a.application_id)
2 from application a, location l
3 where
4 l.location_id = 102884
5 and l.location_id = a.location_id(+)
6* group by l.location_number
LOCATION_NUMBER COUNT(A.APPLICATION_ID)
------------------------------ -----------------------
2323 0
and its returns something
and the select:
select count(*) from application a
where a.posted >= to_date('27-OCT-2000','dd-mon-yyyy') and a.posted <= to_date('22-NOV-2000','dd-mon-yyyy');
Also returns something then the complete seletc should works.
Thats why a want you to do
select count(*) from application a
where a.posted >= to_date('27-OCT-2000','dd-mon-yyyy') and a.posted <= to_date('22-NOV-2000','dd-mon-yyyy');
To have the proof that you have those records between those 2 dates.
-
In the application table you don't have any related records as your first query tells (count=0).
LOCATION_NUMBER COUNT(A.APPLICATION_ID)
------------------------------ -----------------------
2323 0 <==== MEANS THAT THERE ARE NO RECORDS
That's why the query where you use the date condition doesn't return rows.
AP
-
sysdba,
I think you would get the result you expect with this query:
select l.location_number, count(a.application_id)
from location l,
(select * from application
where posted >= to_date('27-OCT-2000','dd-mon-yyyy')
and posted <= to_date('22-NOV-2000','dd-mon-yyyy')) a
where
l.location_id = 102884
and l.location_id = a.location_id(+)
group by l.location_number
/
Try it!
AP
-
I answered this SQL issue in the Database Programming forum.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|