At 2/26/2002 03:55 PM, Ann wrote:

>At 02:53 PM 2/26/2002 -0500, Bob Murdoch wrote:
> 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.

Understood. I'm storing the value without the time part. In fact, in
WISQL, a query on that column returns 'mm/dd/yyyy ', not the
typical 'mm/dd/yyyy hh:mm:ss' it would if I had stored the time. I
understand what you are talking about here, and typically don't rely on a
direct comparison to the date in my apps, just in case.

> 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)?

Account_id is an Integer. The plans for both queries are the same -

PLAN (S ORDER SALE_MDSE_RU_DATE_IDX)


The ddl for the table is:

/* 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));