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
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.
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)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Begoña ,
Try this, it should work:
Select * from table2 where {customfield_1111} = ''
Regards,
Alexandra
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
That won't work, sorry.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I have tested it before posting it. If customfield_1111 is a database information custom field, the given script works.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.