Welcome to NeoOffice developer notes and announcements
NeoOffice
Developer notes and announcements
 
 

Problems after upgrading to Sierra or High Sierra? Try these steps
Problems after upgrading to NeoOffice 2017? Try these steps
Download or installation problems? Try these steps


Support
· NeoOffice Support
· NeoWiki


Announcements
· Twitter @NeoOffice


Downloads
· Download NeoOffice


RSS Feeds
· Announcements Only
· All Posts


  
NeoOffice :: View topic - Treatment of Empty Cells in Base Queries
Treatment of Empty Cells in Base Queries
 
Post new topic   Reply to topic    NeoOffice Forum Index -> NeoOffice Releases
View previous topic :: View next topic  
Author Message
amayze
The Merovingian


Joined: Oct 24, 2005
Posts: 561
Location: Edinburgh, Scotland

PostPosted: Tue Aug 18, 2015 4:13 am    Post subject: Treatment of Empty Cells in Base Queries Reply with quote

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.

Back to top
View user's profile Send private message Visit poster's website
pluby
The Architect
The Architect


Joined: Jun 16, 2003
Posts: 11805
Location: California, USA

PostPosted: Tue Aug 18, 2015 4:44 am    Post subject: Re: Treatment of Empty Cells in Base Queries Reply with quote

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?

Also, if you want to exclude empty cells from your query, you can add "<fieldname> IS NOT NULL" to your SQL statement link below:

Code:
SELECT "Field 1", "Field 2", "Field 3" FROM "Sheet1" AS "Sheet1" WHERE "Field 2" <> 'Y' AND "Field 2" IS NOT NULL


Essentially, that is the result that you got with your original query.

Patrick
Back to top
View user's profile Send private message Visit poster's website
amayze
The Merovingian


Joined: Oct 24, 2005
Posts: 561
Location: Edinburgh, Scotland

PostPosted: Tue Aug 18, 2015 6:17 am    Post subject: Re: Treatment of Empty Cells in Base Queries Reply with quote

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.

Cheers,

Andy
Back to top
View user's profile Send private message Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic    NeoOffice Forum Index -> NeoOffice Releases All times are GMT - 7 Hours
Page 1 of 1

 
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

Powered by phpBB © 2001, 2005 phpBB Group

All logos and trademarks in this site are property of their respective owner. The comments are property of their posters, all the rest © Planamesa Inc.
NeoOffice is a registered trademark of Planamesa Inc. and may not be used without permission.
PHP-Nuke Copyright © 2005 by Francisco Burzi. This is free software, and you may redistribute it under the GPL. PHP-Nuke comes with absolutely no warranty, for details, see the license.
Page Generation: 0.02 Seconds