Is there a JQL equivalent to NVL?

[Andrew Cohen]

Having worked all these years with Oracle, I am wondering if there is an equivalent of NVL(field1, field2) that returns field1 unless field1 is null, in which case it returns field2.

Some of our issue types have due date, some have planned end date. I would like to be able to select NVL(dueDate, plannedEnd) in an issue query. Any way to do this with JQL?

[Nic Brough -Adaptavist- · Accepted solution]

No. It wouldn't really work either - JQL is not SQL and is not for pulling data out, all it does is find a list of issues. There's no such thing as "select" because it looks for issues, not fields.

This NVL function would need to be done in whatever is drawing the results (navigator, gadget, report, etc). To do this, you could write a derived field and report on that.

[Andrew Cohen]

Good point. I guess we need an enhancement request to be able to use Configure Columns to add any function as a column. I notice they give us a few functions already such as Sum of Time Spent, so it must be possible in theory.

Then we would need another enhancement request for an NVL function, which I admit is a bit of a long shot.

Note that if you want to use NVL in the query filter, there is a way to rewrite the expression:

NVL(field1,field2) = value1

to:

(field1 = value1 OR (field1 is empty AND field2 = value1)).

[Andrew Cohen]

What do you mean by derived field?

[Nic Brough -Adaptavist-]

Sorry, I know that's not particularly clear.

A derived field is a field that shows information that is calculated from other data, rather than showing data stored in it.

An example would be the built-in "resolution date" - this field does not allow the users to put data in, and doesn't really store data itself. When it needs to show data, it actually nips into the issue history, looks for the "last time resolution changed from null to not-null" and displays the date of that change. (Ok, that's a lie, technically, it actually does that when you index the issue, and it stores the date in the index files, so you don't hit the database, but you get the point)

You can create derived fields with add-ons, or use the "script runner" add-on to do it for you. They have advantages over simple calculated dsplays because you can index them, and hence search and sort on them.

For your requirement, you'd want to write a rule that works out the NVL, convert it to java (or something for the script runner) and put that in a field.