filter on null boolean field

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
ccmcbride
Posts: 101
Joined: Tue 01 May 2007 16:36

filter on null boolean field

Post by ccmcbride » Wed 04 Jun 2014 20:08

I have a table with boolean field, some of the values are null, not false.
I have a table component, with filter of fieldname = false.

no records are being returned (out of 10 records, 8 have null values, 2 have false values, and I should be getting them all).
Using delphi 2010, Sdac 5.10.0.8.
is there a fix/workaround?

azyk
Devart Team
Posts: 1057
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: filter on null boolean field

Post by azyk » Thu 05 Jun 2014 09:56

Hello,

We couldn't reproduce the problem. Please try to compose a small sample to demonstrate the problem and send it to andreyz*devart*com.

ccmcbride
Posts: 101
Joined: Tue 01 May 2007 16:36

Re: filter on null boolean field

Post by ccmcbride » Thu 05 Jun 2014 19:06

recreated, zipped and emailed.

azyk
Devart Team
Posts: 1057
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: filter on null boolean field

Post by azyk » Tue 10 Jun 2014 11:34

Hello,

We have investigated the provided project. Records from the boolean field value null don't get to your filter, since the null and False values are determined by SQL Server as different values. Therefore, to filter records with null and False values, you should set the TMSTable.Filter property to 'sys_isdeleted = false or sys_isdeleted is null'.

ccmcbride
Posts: 101
Joined: Tue 01 May 2007 16:36

Re: filter on null boolean field

Post by ccmcbride » Tue 10 Jun 2014 15:21

then what is the difference between a filter, and filtersql?

azyk
Devart Team
Posts: 1057
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: filter on null boolean field

Post by azyk » Wed 11 Jun 2014 14:33

Hello,

The TMSTable.FilterSQL value is added to the WHERE clause for the SELECT query. I.e. filtration occurs on the server side.

The value of the TMSTable.Filter property is used on the client side.

ccmcbride
Posts: 101
Joined: Tue 01 May 2007 16:36

Re: filter on null boolean field

Post by ccmcbride » Wed 11 Jun 2014 19:44

if it's used on the client side, then what does it matter what mssql returns? wouldn't it return all records, then be filtered on the value?

azyk
Devart Team
Posts: 1057
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: filter on null boolean field

Post by azyk » Thu 12 Jun 2014 10:37

In this case, SQL Server will return all the records to the dataset. The dataset will contain all the records, but will return only those, that meet the filter condition.

ccmcbride
Posts: 101
Joined: Tue 01 May 2007 16:36

Re: filter on null boolean field

Post by ccmcbride » Thu 12 Jun 2014 15:45

okay, so if I open the dataset, thereby getting all the records, THEN apply the filter, would that work as I expect?

azyk
Devart Team
Posts: 1057
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: filter on null boolean field

Post by azyk » Fri 20 Jun 2014 14:20

Functionality of local filter is similar to functionality of the SQL Server filter. Therefore, when the dataset retrieves all the records, the local filter won't pass records with null values if they are not specified via "is null". Therefore, if you want to retrieve both records with False and null values, you should modify the filter condition to 'sys_isdeleted = false or sys_isdeleted is null'.

Post Reply