I have a Dialect 1 database running on both IB and FB (beta2).

I am trying to select records based on the date in a column declared as
DATE, as well as
some other limiting criteria in the where clause.

When I run the following query I get no rows returned:

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


If I change the above query to include a second account ' (s.account_id in
(1,2)) ', I get the
following results (which are correct):

DEL_DATE ACCOUNT_ID TOTAL
=========== =========== ===========

20-FEB-2002 1 842
20-FEB-2002 2 283

The question is, why can't I get the results for just a single account?


After playing with this a little more, it seems that if I change the date
selection of the original query
to ' (s.del_date between '2/19/2002' and '2/20/2002') ', then I get correct
results for Feb 20 (only - just
one row returned).

Stranger and stranger.

Here is what the table looks like:

/* Extract Table SALE_MDSE_RU */
/* Domain definitions */
CREATE DOMAIN D_DATE AS DATE;
CREATE DOMAIN D_ID AS INTEGER NOT NULL;
CREATE DOMAIN D_ID AS INTEGER NOT NULL;
CREATE DOMAIN D_DEPT AS CHAR(4);
CREATE DOMAIN D_INTEGER AS INTEGER;
CREATE DOMAIN D_SKU AS VARCHAR(20);
CREATE DOMAIN D_VENDOR AS CHAR(3);
CREATE DOMAIN D_YNFLAG AS CHAR(1);
CREATE DOMAIN D_MDSE_DESC AS VARCHAR(35);

/* Table: SALE_MDSE_RU, Owner: SYSDBA */
CREATE TABLE SALE_MDSE_RU (ID D_ID,
ACCOUNT_ID D_ID,
DEL_DATE D_DATE NOT NULL,
DEPT D_DEPT NOT NULL,
VENDOR D_VENDOR NOT NULL,
SKU D_SKU NOT NULL,
QTY D_INTEGER NOT NULL,
PICKED_FLAG D_YNFLAG,
STYLE D_MDSE_DESC,
CONSTRAINT PK_SALE_MDSE_RU PRIMARY KEY (ID));


Tia for any advice,

Bob M..