-
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: steeve_2@videotron.ca
[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?
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
|