InstantDB Project
About InstantDB
Project Mail Lists
Short History
Reporting Bugs
Screen Shots
3rd Party Examples
FAQs

Software
Downloads
Documentation
CVS Repositories
Roadmap
License

About Enhydra.org
Who We Are
News, Articles & Events
Getting Involved
Contact Us

Community
Demos
Contributions
Resources
Case Studies
On The Edge! -NEW-
Commercial Vendors

Kenneth

InstantDB's getting the expression evaluation wrong. Try swapping the order
of the operands so that the constant expression always comes last.

i.e.  SELECT DISTINCT Resurs_id FROM Period
       WHERE Start_tid > {d '2000-07-07'} AND Stopp_tid < {d '2000-07-10'}

I'll see that this gets fixed in the next release.

Regards

Pete

--

Peter Hearty                         peter.hearty@lutris.com
Lutris Technologies (UK)        http://www.lutris.com
----- Original Message -----
From: "Kenneth Ericsson" <kenneth.ericsson@mindproxy.se>
To: <instantdb@enhydra.org>
Sent: Monday, November 06, 2000 2:40 PM
Subject: InstantDB: Problems with, DATE and AND in SQL.


> Hello out there.
>
> I have a problem with dates in my database. When I want to compare if a
> date is inside or outside a given time-period I dont get the expected
> results. Using "bigger than date1 AND smaller than date2" (for detailed
> syntax, see example below) I dont get the right result.
>
> The only comparison that works is if the dates compared are checked for
> equality.
>
> Example:
>
> I have a test table "Period" with 4 rows of data. The rows looks like
> this:
>
> Period_id    Start_tid           Stopp_tid            Resurs_id
> Uppdrag_id
> 1000           2000-08-01    2000-08-15        1000            1000
> 1001           2000-08-03    2000-08-14        1001            1001
> 1002           2000-10-01    2000-10-05        1000            1001
> 1003           2000-10-06    2000-10-10        1001            1001
>
>
> My Sample script looks like this.
>
> e  DROP TABLE  Period;
> e  CREATE TABLE Period
>     (Period_id        INT  PRIMARY KEY AUTO INCREMENT,
>      Start_tid        DATE,
>      Stopp_tid      DATE,
>      Resurs_id      INT,
>      Uppdrag_id   INT);
>
> The mission is:   I want all "Resurs_id" that have periods overlap my
> new period "Tillg_f"  -> "Tillg_t"
>
> This is my debug results coming out of my servlet application.
>
>
>
> Debug info for com.gefionsoftware.instantonline.basic.DBCollectServlet
>
>      Database call info
>        SQL statement: SELECT DISTINCT Resurs_id FROM Period WHERE
> Start_tid > {d '2000-07-07'} AND {d '2000-07-10'} > Stopp_tid
>        Number of rows read/modified: 2
>
>      Request parameters
>        result: Resurs_id.Lista_pa
>        columns: DISTINCT Resurs_id
>        debug: true
>        condition: Start_tid >
> $session|Resurs_sok.prio_1_pa(Tillg_f)||date$ AND
> $session|Resurs_sok.prio_1_pa(Tillg_t)||date$ > Stopp_tid
>        dbname: db_pa
>        table: Period
>        runif: $session|Resurs_sok.prio_1_pa(Kat)$ = 2
>
> As you can see the database found 2 "Resurs_id" there it shuld be none.
> It seems like the expr. with " > " doesn't work. When I test with " = "
> it seems to work and when I test the expr. one and one it also works.
> My version of instantdb is 2.41..
>
> Can anyone help me?
>
> Regards!!
>
>
>
> // Kenneth Ericsson
> MindProxy AB  Sweden.
>

-----------------------------------------------------------------------------
To unsubscribe from this mailing list, send email to majordomo@enhydra.org
with the text "unsubscribe instantdb" in the body of the email.
If you have other questions regarding this mailing list, send email to
the list admin at owner-instantdb@enhydra.org.