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- · Accepted solution]

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.

[Udo Brand]

I disagree. It is possible to search for non existing records. select * from jiraissue i where not exists (select id from customfieldvalue cfv where cfv.customfield =10000 and cfv.issue = i.id)

[Nic Brough -Adaptavist-]

Yes, that's what I meant by "directly" - in your SQL, it's indirect - you're taking a list of everything that might be there and dropping the stuff that does exist. You end up with a list of issues, not a list of empty customfields. It is right, of course, but it's not direct.

[Alexandra Topoloaga]

Hi Begoña ,

Try this, it should work:  
 Select * from table2 where {customfield_1111}  = ''

Regards,
Alexandra 

[Nic Brough -Adaptavist-]

That won't work, sorry.

[Alexandra Topoloaga]

I have tested it before posting it. If customfield_1111 is a database information custom field, the given script works.

[Nic Brough -Adaptavist-]

My mistake, I thought he was talking about normal custom fields, where it absolutely will not work. The DBCFs are an exception, as they hold blanks.

[Begoña Bonet]

Many thanks you two!!!