I created two tables that match the names and columns of your query and it
works. The table creation DDL is as follows:

CREATE TABLE "groupmembership"("user" INTEGER, "group" INTEGER);
CREATE TABLE "groupcapability"("group" INTEGER, "capability" INTEGER);

It is obvious that one or both the tables were not defined with quoted
names, in which case their names would be interpreted by any SQL92 compliant
DB as all uppercase and would not match the quoted lowercase names.

Try:

SELECT * FROM groupmembership WHERE "user" = 1 AND EXISTS ( SELECT
groupcapability."group" FROM groupcapability, "groupmembership" WHERE
groupcapability."group" = groupmembership."group" AND "capability" = 0)

If you want to see the exact DDL as interpreted by HSQLDB, run
DatabaseManager, open the database, and issue the SCRIPT command. All
unquoted names will be unquoted in uppercase and all quoted names will be
quoted as they were defined.

Fred

----- Original Message -----
From: "Tim Pizey" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: 25 May 2002 19:35
Subject: [Hsqldb-developers] Quoting problem


Hi Fred,

Here is another little one, I have quoted everything and come up with this

SELECT * FROM "groupmembership" WHERE "user" = 1 AND EXISTS ( SELECT
"groupcapability"."group" FROM "groupcapability", "groupmembership" WHERE
"groupcapability"."group" = "groupmembership"."group" AND "capability" = 0)
Error reported by jdbc driver
Column not found: S0022 Column not found: group in statement [SELECT * FROM
"groupmembership" WHERE "user" = 1 AND EXISTS ( SELECT
"groupcapability"."group" FROM "groupcapability", "groupmembership" WHERE
"groupcapability"."group" = "groupmembership"."group" AND "capability" = 0)]

Is there an obvious way around this problem?

cheers
timp


On Saturday 25 May 2002 17:22, Fred Toussi wrote:
> Thanks Tim,
>
> We have made a big effort to extend support for SQL and JDBC standards as
> well as improvements in other areas so that HSQLDB can be used with other
> products with fewer problems. Please let us know about any other potential
> issues.
>
> Fred

_______________________________________________________________

Don't miss the 2002 Sprint PCS Application Developer's Conference
August 25-28 in Las Vegas -- http://devcon.sprintpcs.com/adp/index.cfm

_______________________________________________
hsqldb-developers mailing list
[EMAIL PROTECTED]
https://lists.sourceforge.net/lists/listinfo/hsqldb-developers


_______________________________________________________________

Don't miss the 2002 Sprint PCS Application Developer's Conference
August 25-28 in Las Vegas -- http://devcon.sprintpcs.com/adp/index.cfm

_______________________________________________
hsqldb-developers mailing list
[EMAIL PROTECTED]
https://lists.sourceforge.net/lists/listinfo/hsqldb-developers

Reply via email to