Joined: Oct 24, 2005 Posts: 561 Location: Edinburgh, Scotland
Posted: Tue Aug 18, 2015 4:13 am Post subject: Treatment of Empty Cells in Base Queries
I have a Base document that uses an .ods spreadsheet as its data source. One field either contains 'Y', '#' or is empty. Before NeoOffice 3.4.1 Patch 13 querying for records where the field did not contain 'Y' would return records where the field contained '#' or was empty. From Patch 13 and onwards the same query only returns records where the field contains '#' and fails to match empty fields.
In note from the release notes of Patch 13 that:
When querying for null values in spreadsheet data sources, empty cells would be treated as if they were not null
Is what I am seeing an unintended side effect of this change, or is it what is supposed to happen?
In theory I should be able to query for fields which contain either '#' OR are empty, but unfortunately the built in database engine that is used for spreadsheet data sources cannot cope with NULL as a search criteria and querying for fields which contain the empty string '' returns no matches, despite there being empty fields in the data base.
I attach a very simple spreadsheet sand also the associated database file.
If the database does not work:
Create a new one with File - New - Database.
Then select Connect to an existing database and choose Spreadsheet in the drop down menu.
Click Next
Click Browse and locate the Spreadsheet file
Click Next
Click Finish
Select Queries on left hand side of Database window
Click Create Query in SQL View...
Paste in the following:
Code:
SELECT "Field 1", "Field 2", "Field 3" FROM "Sheet1" AS "Sheet1" WHERE "Field 2" <> 'Y'
Close the window and save the Query
Double click on the Query and observe that only one record is shown in which Field 2 is neither 'Y' or Empty.
Joined: Oct 24, 2005 Posts: 561 Location: Edinburgh, Scotland
Posted: Tue Aug 18, 2015 6:17 am Post subject: Re: Treatment of Empty Cells in Base Queries
pluby wrote:
amayze wrote:
In note from the release notes of Patch 13 that:
When querying for null values in spreadsheet data sources, empty cells would be treated as if they were not null
Is what I am seeing an unintended side effect of this change, or is it what is supposed to happen?
You are correct. What you are seeing is a result of backporting OpenOffice's treatment of empty spreadsheet cells.
So, to select empty cells from a spreadsheet in Base, you need to add "<fieldname> IS NULL" to your SQL statement like below:
Code:
SELECT "Field 1", "Field 2", "Field 3" FROM "Sheet1" AS "Sheet1" WHERE "Field 2" <> 'Y' OR "Field 2" IS NULL
Does the above SQL statement work for you?
Ah! I was missing out the 'IS'. Using design view you need to include the operator unless it is '=' when it is assumed and added by NeoOffice, however entering 'EMPTY', which is then changed to 'NULL' in the SQL produces bad SQL as '= NULL' is not valid syntax, however now I've entered 'IS EMPTY' the SQL is correct and the query runs.
So now the query above returns records in which the value of Field 2 is not 'Y' or is empty, which is what I want.
Thanks for the quick response Patrick. I will now go back and install the latest patch to NeoOffice 3.4.1 and update the query in my real database.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum You cannot attach files in this forum You cannot download files in this forum
