At 2/26/2002 12:57 PM, you wrote:

>So, does that mean that it still doesn't work even using between with the
>same date in both places?

Yes. It does not work using the equivalence test or with the 'between'
operator.

>Or, are you illustrating a possible bug in the query engine with the
>simplified query.

Boy, I always hate to see people say 'I found a bug', but in this case I'll
be darned if I can figure out how the query is wrong.


To summarize:

When I run the following query I get no rows returned (this on IB or FBrc2):

select
s.del_date, s.account_id, sum(s.qty) as Total
from
sale_mdse_ru s
where
(s.del_date = '2/20/2002') and
(s.account_id in (1)) and
(s.dept = 671)
group by
s.del_date, s.Account_id

Changing the date in where clause to '(s.del_date between '2/20/2002' and
'2/20/2002')' has no effect. Changing the account in the where clause to
'(s.account_id = 1)' has no effect.

Adding a second account ( 'where s.account_id in (1,2)' ) solves the
problem. Adding a different date to the between clause returns rows except
those matching the first date specified ('s.del_date between '2/19/2002'
and '2/20/2002' will return a row for 2/20 only).


I am unfamiliar with the engine, so therefore refrain from speculating as
to whether this is a bug or just my fumble-fingered-ness.


Bob M..