Hi,
This looks like a bug in OpenOffice.
SELECT * FROM "TEST"."AAA"."CONTAINER_USE" WHERE
( "TEST.AAA.CONTAINER_USE"."BATCH" IS NOT NULL )
The column name is quoted incorrectly. Quoting is part of the ANSI SQL
standard, I don't know a database where this statement is works. In
this query, there is no table named "TEST.AAA.CONTAINER_USE" (as one
word). There is a table "CONTAINER_USE". The correct statement would
be:
SELECT * FROM "TEST"."AAA"."CONTAINER_USE" WHERE
( "TEST"."AAA"."CONTAINER_USE"."BATCH" IS NOT NULL )
This would work in H2. Here is a complete test case. The database name
needs to be 'test' (case insensitive), that means for this example it
is important that the JDBC URL ends with "test":
jdbc:h2:~/test
drop schema aaa;
create schema aaa;
create table aaa.container_use(batch varchar);
insert into aaa.container_use values('x'), (null);
SELECT * FROM "TEST"."AAA"."CONTAINER_USE" WHERE
( "TEST"."AAA"."CONTAINER_USE"."BATCH" IS NOT NULL );
Regards,
Thomas
On Wed, Sep 30, 2009 at 10:40 PM, abc6587 <[email protected]> wrote:
> In an Open Office spreadsheet, press f4 to view the datasources, select any
> H2 table, then add a filter on any field (I added a non-null filter). This
> results in an error message like this:
>
>
> ERROR: Column TEST.AAA.CONTAINER_USE.BATCH not found; SQL statement:
> SELECT
> * FROM "TEST"."AAA"."CONTAINER_USE" WHERE
> ( "TEST.AAA.CONTAINER_USE"."BATCH" IS NOT NULL ) [42122-111]
>
> Query
> = SELECT * FROM "TEST"."AAA"."CONTAINER_USE" WHERE
> ( "TEST.AAA.CONTAINER_USE"."BATCH" IS NOT NULL )
>
> Same query fails standalone as well.
>
> Not sure whether what they are doing is standard, but I presume it works
> with other databases, and regardless, it would be neat to have H2 compatible
> with Open Office.
>
> Thanks
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en
-~----------~----~----~----~------~----~------~--~---
