JQL JIRA query for custom field being equal to two other custom field values

[Nicholas Rose]

I want to query my project for all issues where 

customField1 != value(customField2) + value(customField3)

Is it possible to do this in a JQL query?

I tried "cf[123]!=cf[456]+cf[789]", but that didn't work.

[Udo Brand · Accepted solution]

Scriptrunner has a scripted JQL function which could help as long as your custom fields are date, datetime or numeric fields.

see here: https://scriptrunner.adaptavist.com/latest/jira/jql-functions.html#_expression

[Nicholas Rose]

This is cool, but I really think another library is overkill for this. All I want to do is a pure JQL jquery that checks for one field being equal to the sum of two others. Surely there's a way to do this in pure JQL.

[Udo Brand]

The JQL functions provided by Scriptrunner plugin can just do this. Your attempt will never work since you can't compare two (or more) fields in out of the box JQL.

[Vasiliy Zverev]

You can try using Script Field provided ScriptRunner plugin. Here is code example to get diffenrence between two date custom fields:

import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.issue.fields.CustomField

import java.sql.Timestamp

/**
 * Get number of days between to customfields
 */

CustomField startDate = ComponentAccessor.getCustomFieldManager().getCustomFieldObjectByName("Дата выдачи согласованного MD.050");
CustomField endDate = ComponentAccessor.getCustomFieldManager().getCustomFieldObjectByName("Дата выдачи в тестирование.План");

if ((startDate == null) && (endDate == null))
    return ""

long startDateInMls = ((Timestamp) startDate.getValue(issue)).getTime();
long endDateInMls = ((Timestamp) endDate.getValue(issue)).getTime();


if ((startDateInMls == 0) && (endDateInMls == 0))
    return ""

return "" + ( startDateInMls - endDateInMls)/(1000*60*60*24)

This plugin is avalable only for server version.

[Nicholas Rose]

Hm I really need a pure JQL query to do it, because I'm just using a node.js app to run this. It should be very lightweight, and this is really the only thing it does, so there should be a way to do this just in JQL

[Stephen Deutsch]

Are you trying to say that customfield1 should be not equal to neither customfield2 nor customfield3?

In that case it would be like this: cf[123]!=cf[456] AND cf[123]!=cf[789]

If you are trying to actually add the fields together, that would be a different query.

[Nicholas Rose]

I'm trying the latter, unfortunately. I'm looking for cf[123] where cf[123] is not equal to the sum of two other custom field values.

[amit.jadhav]

@Stephen Deutschcan you let me know is the above query JQL of it needs different plugin to execute?

Also Can you share any reference link around the same