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

3

5

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:

enter image description here

enter image description here

Rein answered 27/8, 2015 at 15:32 Comment(3)
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
P
8

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

Phosphoric answered 27/8, 2015 at 20:18 Comment(4)
seem that ssdt have a lot of bugsRein
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
L
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 answered 30/9, 2016 at 17:44 Comment(0)
L
-1

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 answered 29/8, 2015 at 9:14 Comment(1)
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 formRein

© 2022 - 2024 — McMap. All rights reserved.