Visual Studio 2013 SSDT - Edit data - IS NULL not work as filter

Question

I am trying to filter some row of a table with ssdt (left click on table, view data, sort and filter)

Here I simply need to add IS NULL as a condition to an nvarchar field.
But as soon as I apply filter I get the error:

Incorrect syntax near the keyword SET

Looking at the query written by editor I see that the consition is fldName =, no sign of my NULL check

How can I do it?

This is th result:

SELECT TOP 1000 [Ktyi_TS002_IdTipoDocumento] ,
[nvc_TS002_TipoDocumento] ,[nvc_TS002_IdFunzioneControllo] ,[bit_TS002_Annullato] 
FROM [dbo].[TS002_TipoDocumento] 
WHERE [nvc_TS002_IdFunzioneControllo] =

this is some images of the data editor found in google to show what iam talking about to who don't know ssdt:

Rein

Can you post the query by the editor ? – Lentiginous

can you elaborate more on what you need to do? Do you need to set a constraint for a column in your table to indicate that it may hold a null value or do you need to set a default value for that column to be null ? – Lentiginous

have you never use SSDT to edit data? seriously i can't understand how my question is not clear for you. have you try to do what i write: left click on table, view data, sort and filter and set IS NULL in condition column? – Rein

Answer 1

It seems to be a bug that IS (NOT) NULL expression is not supported in the filter.

Phosphoric

seem that ssdt have a lot of bugs – Rein

It is still not supported. – Pitts

And still not supported. Knowing that this problem stil exists after such a long time, and so few people are concerned, makes me think I should give up trying to use this tool. – Vibraphone

See answer below for the workaround to this bug/lack of feature. – Reconvert

Answer 2

This is a very ugly hack, but it may work for you.

It seems like you need a column name on the left of the = sign to keep the filter parser from changing the query. In my case my column that I was looking for nulls in was an integer, so I needed to get an integer on the left hand side.

I also needed a value for the columns that I was looking for nulls in that would not exist for any non-null row. In my case this was 0.

Create MyTable 
(  Id    int primary key,
     ...
   MyNum  int
);

To search for rows with nulls in column MyNum, I did this:

[Id] - [Id] = IsNull([MyNum],0)

The [Id] - [Id] was used to produce 0 and not trigger the parser to re-write the statement as [MyNum] = stuff

The right hand side was not re-written by the parser so the NULL values were changed to 0's.

I assume for strings you could do something similar, maybe

concatenate([OtherStringCol],'XYZZY') = ISNull([MyStrCol],concatenate([OtherStringCol],'XYZZY'))

The 'XYZZY' part is used to ensure that you don't get cases where [MyStrCol] = [OtherStringCol]. I am assuming that the string 'XYZZY' doesn't exist in these columns.

Low

Answer 3

You can work around this by creating a new SQL query for that table and type the filter for "IS NULL" manually.

1- Right-click the table
2- Click "New Query"
3- Type the SQL statement using IS NULL as a filter

This should work fine.

Lentiginous

how do you think to be able to edit data from a query? As i write in title and in comments iam talking about how to filter row in the EDIT DATA form – Rein