weird SQL result

[sysdba]

Since last thread is too long, I begin a new one:

create table m (m_id int primary key, des varchar2(32));
create table d (d_id int primary key, posted date, m_id int);
alter table d add constraints d_fk foreign key (m_id) references m(m_id);

insert into m values (1, 'TEST1');
commit;


select m.DES, count(d.d_id)
from m, d
where m.m_id = d.m_id(+)
and m.m_id = 1
group by m.DES
/

DES COUNT(D.ID)
-------------------------------- -----------
TEST1 0

select m.DES, count(d.d_id)
from m, d
where m.m_id = d.m_id(+)
and m.m_id = 1
and d.posted >= to_date('27-OCT-2000','dd-mon-yyyy')
and d.posted <= to_date('22-NOV-2000','dd-mon-yyyy')
group by m.DES
/

no rows selected

Question is: Why not the second query return:
DES COUNT(D.ID)
-------------------------------- -----------
TEST1 0

Note: I tested this is not date comparsion issue,
nor to_char, nor to_date issue.

Thanks!!!

[nirasha]

I know normally even though when you display fields it appears in a certain format, they are not stored like that.

I have alot of faith in using the function on the fields themself before doing the comparison. This ensures that you are converting (albeit just the display) the values prior to comparison. and it sounds weird but please try it with the to_char as I suggested. Normally it does not pick up the values/row because it is comparing with some silly format.

Anyway, trying it would not hurt.

Nirasha

[sysdba]

nirasha,

As I said this is not format, to_char, to_date issue!

OK, look this one:

create table m (m_id int primary key, des varchar2(32));
create table d (d_id int primary key, another_id int, m_id int);
alter table d add constraints d_fk foreign key (m_id) references m(m_id);

insert into m values (1, 'TEST1');
commit;

select m.DES, count(d.d_id)
from m, d
where m.m_id = d.m_id(+)
and m.m_id = 1
group by m.DES
/

DES COUNT(D.ID)
-------------------------------- -----------
TEST1 0

select m.DES, count(d.d_id)
from m, d
where m.m_id = d.m_id(+)
and m.m_id = 1
and d.another_id = 100
group by m.DES
/

no rows selected

[tamilselvan]

The 1st Select Statement does not have date check even though the table d is outer joined.

Where as the 2nd statement does check the date and since there is no data in the table d, it returns 0 rows.

[steeve123]

Ok, this is to reply to nirasha.

COMPARING CHARACTER WHEN YOU R FEILD IS A DATE IS NOT GOOD!!!!!!!!
GOD DAMN!!!!!
OK HERES THE PROOF:
make the following select :
select 'allo' from dual where to_char(sysdate,'dd-mon-yyyy') > '01-APR-2001';
'ALL
----
allo

1 row selected.


sysdate = '29-NOV-2000'
my string is = '01-APR-2001'

IS 29 NOVEMBER 2000 IS GREATER THAN 01 APRIL 2001!!!! NO!!!!!

IT SHOULD RETURN THE FOLLOWING IF YOU MAKE THE GOOD WHERE CLAUSE:


SQL> select 'allo' from dual where sysdate > to_date('01-APR-2001','dd-mon-yyyy');

no rows selected


SEE.:)

thats why i want you to understand that when you have a date in a table and you want
to make a where on it, compare it with a date not a character.



[Edited by steeve123 on 11-29-2000 at 04:33 PM]

[steeve123]

And sysdba, did you did
Select count(*) from application
where
posted >= to_date('27-OCT-2000','dd-mon-yyyy')
and d.posted <= to_date('22-NOV-2000','dd-mon-yyyy');


Cuse if that dont returns rows, then it wont returns in


select m.DES, count(d.d_id)
from m, d
where m.m_id = d.m_id(+)
and m.m_id = 1
and d.posted >= to_date('27-OCT-2000','dd-mon-yyyy')
and d.posted <= to_date('22-NOV-2000','dd-mon-yyyy')
group by m.DES
/

[nirasha]

Thanks, I guess in my previous place (where it worked) it was not stored in date format, and we were doing = comparison , so it worked because it was basically a string comparison.

Thanks for the info tho, just thought it was an option to explore because often comparing two different types don't work.

Nirasha

[sysdba]

OK, I finally found it!!!!!!!!!!!!!!

select m.DES, count(d.d_id)
from m, d
where m.m_id = d.m_id(+)
and m.m_id = 1
and d.posted (+) >= to_date('27-OCT-2000','dd-mon-yyyy')
and d.posted (+) <= to_date('22-NOV-2000','dd-mon-yyyy')
group by m.DES
/

Do not know why though :-(

Thanks everyone!