At 02:53 PM 2/26/2002 -0500, Bob Murdoch wrote:

>When I run the following query I get no rows returned (this on IB or FBrc2):
>...
>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.

Bob,

First, in a dialect 1 database, the data datatype includes a time
portion. You may have stored some value for time of day. That would
explain why
s.del_date between '2/20/2002'
s.del_date between '2/20/2002' and '2/20/2002'
don't work. Nothing happened at exactly the stroke of midnight between
the 19th and the 20th. However, it doesn't explain why
s.del_date between '2/19/2002' and '2/20/2002'
returned values for the 20th. It should have returned values for everything
starting at the midnight between the 18th and 19th, up to but not including
things that started at the midnight between the 19th and 20th.

Why changing something from
s.account_id in (1)
to
s.account_id in (1,2)
should have any effect I haven't a clue. Knowing all the datatypes - you're
not using floating point for your account id are you? - might help. So would
the plan generated for the query. Are the plans the same for s.account_id
in (1)
and s.account_id in (1,2)?

Regards,


Ann