weird SQL

[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 >= '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,

[tamilselvan]

Add time along with the date since date column stores date and time in that column.

[sysdba]

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

[steeve123]

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]

[sysdba]

posted is date field.

Why not you can compare date with date?
Note: My nsl_date format is 'DD-MON-YYYY'

[steeve123]

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.

[sysdba]

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

[steeve123]

desc application;

i want to see your type plz:

[sysdba]

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,

[steeve123]

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?