Why a query on an ID field with ~ 0 returns also those fields that have =! 0?

[stefano reghezza]

And the next question, how can I build a query on such field so that it just returns the rows where the ID field is null? At the Moment with ~0 I seem only to be able to get both (null and not null)..

For instance, I'm using: Problem-ID ~ 0 AND Incident-ID ~ 0 

This returns both rows, with or without Problem-ID, I'd like only those with no Problem ID to be returned.

Is 0 or Is Null or is EMPTY seems not to be accepted/ working.

Any help would be much appreciated. Thank you!

[Nic Brough -Adaptavist- · Accepted solution]

Two things here:

• 0 is not empty or null, it's a value.
• "~" means "like", not "equivalent to", it's a fuzzy search which will pull back things that are similar to the search term.

If you want to get genuinely empty rows, then "incident-id is empty" is the search to use.  A fuzzy search is going to find you IDs that might have 0s in them.  I'm not sure of the format of your ID here, but it sounds like you might have used the wrong type of field for it if you want exact searches.

[stefano reghezza]

Hi Nic, first of all thanks a lot for your answer. What you say makes sense. I see our Problem and Incident ID fields with no values show 0 which could be because this value is set as the default for the column (don't have insight on the DB objects)..

When I specify Problem-ID = 0, though, the following is returned:

The operator '=' is not supported by the 'Problem-ID' field.

If I specify Problem-ID is 0, I get:

Operator 'is' does not support searching for non-empty values for field 'Problem-ID'.

I guess that was the reason why ~ was specified to start with.

If I specify 'is EMPTY' I get no rows back (probably because of the values being set to 0 if nothing is specified).

I was hoping to be able to filter on the rows that have Problem ID set to 0, but somehow I don't seem to be able to do this.

[Nic Brough -Adaptavist-]

Ok, you've not got empty fields, although filling it with 0 is something you probably didn't want to do, because they're taking up space in the database and remain non-searchable.

You've used a text field, so you only have the fuzzy "like" search, and with the 0's being added, you can't use "is empty".

Essentially, you have the wrong field type for your external ID.  The only way I think we can fix this is by using scripts to either represent it in a different field type, or move the data into a more suitable field.

[stefano reghezza]

Okay, I guess the point that text field with 0 default isn't searchable answers my question.

I didn't set it up this way, but I can understand now what is going on.

Thanks a lot.