-
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 >= '27-OCT-2000' and a.posted <= '22-NOV-2000'
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 >= '27-OCT-2000' and a.posted <= '22-NOV-2000'
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.
Thanks,
-
Add time along with the date since date column stores date and time in that column.
-
Tamil,
That will not help:
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 trunc(a.posted) >= '27-OCT-2000' and trunc(a.posted) <= '22-NOV-2000'
group by l.location_number
/
Still:
no rows selected
-
Your field posted is a date or a varchar2, cuse in your where, you are comparing character, not date.
Instead, do the following:
and a.posted >= to_date('27-OCT-2000','dd-mon-yyyy') and a.posted <= to_date('22-NOV-2000','dd-mon-yyyy')
Steeve Bisson
EMail: [email protected]
[Edited by steeve123 on 11-29-2000 at 02:27 PM]
-
posted is date field.
Why not you can compare date with date?
Note: My nsl_date format is 'DD-MON-YYYY'
-
a.posted >= '27-OCT-2000' and a.posted <= '22-NOV-2000'
definition of your where clause:
a.posted = date
'27-OCT-2000' = varchar2
your are comparing a date with a character, thats not good.
if you want that your select returns something
you have to compare 2 fields of same type
so thats why you have to make a to_date on your '27-OCT-2000'
so try the where i posted earlier.
-
OK, even change to char, does not work:
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 to_char(trunc(a.posted)) >= '27-OCT-2000' and to_char(trunc(a.posted)) <= '22-NOV-2000'
group by l.location_number
/
no rows selected
Chnage to date, does not work either:
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 trunc(a.posted) >= to_date('27-OCT-2000', 'DD-MON-YYYY')
and trunc(a.posted) <= to_date('22-NOV-2000', 'DD-MON-YYYY')
group by l.location_number
/
no rows selected
-
desc application;
i want to see your type plz:
-
Here we go...
SQL> desc application
Name Null? Type
------------------------------- -------- ----
APPLICATION_ID NOT NULL NUMBER(11)
POSTED DATE
LOCATION_ID NUMBER(11)
...
Note: APPLICATION_ID is my PK while location_id is FK
Thanks,
-
Now do the following:
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');
Do you have records?
If yes , then thats the where you have to put:
and a.posted >= to_date('27-OCT-2000','dd-mon-yyyy') and a.posted <= to_date('22-NOV-2000','dd-mon-yyyy')
not
and trunc(a.posted) >= to_date('27-OCT-2000', 'DD-MON-YYYY')
and trunc(a.posted) <= to_date('22-NOV-2000', 'DD-MON-YYYY')
DONT PUT THE TRUNC FUNCTION, I NEVER TOLD YOU TO PUT THE TRUNC FUNCTION.
If theres no records after the following:
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');
then make a select * form application a order by a.posted;
Do you see a records that is between those 2 dates?
-
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.
