How can I define a SQL on which, in where clause, I express that a Database Customfield has no value?

Begoña Bonet

Hi, 

How can I test if a Database Customfield is null in a Dynamic query?

My scenario: 

DBCF1 named customfield_1111: Select * from table1 
This DBCF1 has "Add None option" checked. 

DBCF2 named customfield_2222: 
Select * from table2 where {customfield_1111} is not null  --> it works 
Select * from table2 where {customfield_1111} is null         --> it doesn't work 
Select * from table2 where {customfield_1111}='None'       --> it doesn't work 
Select * from table2 where {customfield_1111}='Ninguno'  --> it doesn't work 

How can I define a SQL on which, in where clause I can indicate "customfield_1111 has no value"? 

Many thanks 

Begoña 

Nic Brough -Adaptavist-

The short answer is that you can't search directly.  SQL searches columns and rows in a table.

When a custom field is empty, there is no row in the table to search for.  So SQL won't find anything.  That's also why  "select where x = "" " will fail - x does not contain <blank>, x is not there and hence cannot be tested.