Is there a JQL equivalent to NVL?

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?

1 answer

1 accepted

0 vote

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.

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)).

What do you mean by derived field?

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.

Suggest an answer

Log in or Sign up to answer
How to earn badges on the Atlassian Community

How to earn badges on the Atlassian Community

Badges are a great way to show off community activity, whether you’re a newbie or a Champion.

Learn more
Community showcase
Published Sunday in Agility

You asked for it, so we delivered: images on issues have arrived

A picture tells a thousand words. And agility boards have just released their latest feature: cover images on issues – so now your board can tell a story at first glance. Upload attachmen...

340 views 3 11
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you