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

Sascha

OK - got it. You either need to add a line of SQL before the query:

SET LITERALS STRICT_ON

Or you need to add modify the line in sample.prp from:

    strictLiterals=0

to

    strictLiterals=1

By default, InstantDB interprets "mycol" as a column name if it can find a
column name that matches. Setting the above property forces everything in
quotes to always be treated as a string literal. I had this switched on in
the database I tried your script with, which is why I didn't get it to work.
If you want to know why it does this, visit:

http://instantdb.enhydra.org/software/documentation/types.html#StringLiteral
s

So...

SELECT DISTINCT unitId FROM UnitOptions
WHERE fieldname="targetArea" AND value="Berlin";

There's no column called targetArea in the above query, so it behaves as
expected.

SELECT * FROM Unit
WHERE Unit.id IN
    (SELECT DISTINCT unitId FROM UnitOptions WHERE value="Berlin");

Again no ambiguity.

SELECT * FROM Unit
WHERE Unit.id IN
    (SELECT DISTINCT unitId FROM UnitOptions
    WHERE fieldname IN ("targetArea") AND value="Berlin");

Here, columns ar not allowed in the IN clause, so no attempt is made to see
if targetArea could be a column.

SELECT * FROM Unit
WHERE Unit.id IN
    (SELECT DISTINCT unitId FROM UnitOptions
    WHERE fieldname="targetArea" AND value="Berlin");

Here, targetArea gets picked up from the outer select, so InstantDB will try
to perform a correlated sub-select. This returns no value because there are
no rows where Unit.targetArea=UnitOptions.fieldname.

Regards

Pete

--

Peter Hearty                         peter.hearty@lutris.com
Lutris Technologies (UK)        http://www.lutris.com
----- Original Message -----
From: "Sascha Matzke" <sascha@bespin.de>
To: <instantDB@enhydra.org>
Sent: Tuesday, October 03, 2000 3:36 PM
Subject: Re: InstantDB: select madness


> Hello,
>
> On Tue, Oct 03, 2000 at 03:19:27PM +0100, Peter Hearty wrote:
> > What version of InstantDB are you using?
>
> 3.21 but the database was created with 3.20beta2
>
> > > SELECT * FROM Unit WHERE Unit.id IN (SELECT DISTINCT unitId FROM
> > > UnitOptions WHERE fieldname="targetArea" AND value="Berlin")
> > >
> > > So... The above doesn't work...
>
> > Do you get an exception, or does it return the wrong data? If the
> > wrong data then too much or too little or just the wrong value?
>
> I get no exception... no data either..
>
> > A script that creates the two tables, adds some data to illustrate
> > the problem would really help.
>
> I attached a sample script...
>
> Sascha
> --
> .-> Sascha Matzke - sascha@bespin.de ------------------------.
> |   Eure Moral, Eure MGs werden nicht ewig regier'n...       |
> |                                         Anarchist Academy  |
> `-- On this earth for  24 years,  355 days  <----------------'
>
>

-----------------------------------------------------------------------------
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.