----- Original Message -----
From: "Bob Murdoch" <ram5@...>
To: <ib-support@yahoogroups.com>
Sent: Tuesday, February 26, 2002 10:50 AM
Subject: [ib-support] Strange query results, based on where clause


> 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..
>
>
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>
>