SELECT result from JQL

Ram Muthiah October 1, 2013

Is there a way I can select specific sub-set of result from JQL? Let us say that I do JQL like

project = "My project" and timeSpent is not null

Instead, I want to do

SELECT timespent WHERE project = "My project" and timeSpent is not null

Is it possible in JQL? Basically, I want this to work like regular SQL query. Is there another way to accomplish this?

2 answers

1 accepted

2 votes
Answer accepted
Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
October 1, 2013

JQL is not SQL. JQL returns a list of issues, and that's it. It has nothing to say about the output.

Remember that there are loads of ways of displaying search results from Jira. Your "select" would be not only useless but actively counterproductive in a lot of places - for example if you used that filter in the "filter statistics gadget", because that needs fields other than time spent.

So, no, you need to separate out "searching for issues with JQL" from "displaying information about the issues".

Ram Muthiah October 9, 2013

Thanks Nic. In my example above, I can export the results to Excel from JQL

project = "My project" and timeSpent is not null

and calculate the timespent. Is there any other way of "displaying information about the issues"?

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
October 9, 2013

Loads - the issue navigator is quite customisable, there are reports and gadgets built-in and you can always add plugins if the built-in stuff doesn't work. All of the reports are more clever than excel, although they all have a specific output which may not be what you want.

0 votes
Pablo Beltran
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
February 28, 2016

This is easy to do with SQL for JIRA:

 

SELECT timespent WHERE  JQL='project = "My project" and timeSpent is not null'

Pablo Beltran
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
February 28, 2016

Nick, Thanks for recommending SQL for JIRA in private. smile . However, a lot of users read old questions AT THE PRESENT and SQL for JIRA is a right, valid and up to date ANSWER. It's frequent that vendors that create a new plugin or support a new feature post and answer even years ago. From time to time I got emails of answers which a vendor has posted in an answer and the original question is from 2012 or 2013, 2014.. but you do not down vote them nor say "this is spam". So your action is personal and selective for SQL for JIRA. I'm just writing a complaint about you after seeing you have voted SQL for JIRA -1 a lot of times today.

FYI: Today at he morning, SQL for JIRA has become free due the difficulties in selling it. THANKS FOR YOUR VALUABLE HELP:

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
February 28, 2016

It's free?  That's excellent news!  That's removed my "it's an advert" objection.

It is not a personal thing, other users who have necro-posted adverts get voted down too.

I'm happy to undo the negative votes now that it's free, but I'd still ask you to stop the necro-postings, as they're pretty annoying (but an annoying I will ignore now)

Pablo Beltran
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
February 28, 2016

Ah! Nick, it is free then... is not spam yet? Incredible ridiculous answer.

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
February 28, 2016

P.s. I hope you don't mind that I removed the previous comments, there's no need for end-users to see us wrangling.  Hopefully, we can leave it as "agree to disagree" here, and be at peace. 

I'm sorry I've upset you with this, it was just something I found very very irritating, and I should not really have let it get to me.

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
February 28, 2016

Sorry, wasn't clear there - "spam advert", not spam.  If something is free, then it doesn't feel like you are going to get a direct gain from an advert.

Pablo Beltran
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
February 28, 2016

No Nick, no. If you do not like vendors earn money is a very different issue than spamming.

FYI:  I'm not need to be at peace. Simply, I'm not in war with you nor nobody. I simply tried to help users to resolve their issues. Perhaps I would earn some dollar with it, of course. But I tried to be honest and answer questions that SQL for JIRA can really resolve.

 

Pablo Beltran
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
February 28, 2016

Nick, I've made some investigations in order to be how much honest you are.

You have voted -1 for my answer on 23, Feb 2016 for this question:

https://answers.atlassian.com/questions/37038645

On the same day, few minutes before than me, @Jamie Echlin [Adaptavist] who works at adaptavist answered this:

"...If you have a commercial version please feel free to create a support request:..."

It's difficult for me imagine a more comercial answer like the Jamie's one asking for a commercial license in order to provide support to a user. However, you voted -1 for me and not for Jamie who "casualty" is employee at your organization. In my opinion, this demonstrates how you are using your reputation on Answer to damage the reputation of your competitors.

If it was truth what you say about your negative votes for commercial answers in general, you should also vote -1 to your organization's answer which is trying to earn money without any doubt. Does it not irritate you then? Does not irritate that your company try to earn money to pay your salary? But of course, if other people try to earn money then... 

Shame on you!!!!!!!

 

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
February 28, 2016

The question was already about Script Runner, so his answer was a valid one.

Your answer was not particularly relevant in my opinion, and you were annoying me with your irrelevant necro-posting at the time.  It's nothing to do with competition, it's because I'm a grumpy old man who doesn't like necro-posting.  I'd vote down my colleagues if they did that as well.

Please stop taking this personally, and please stop wasting our time on it.  It's not personal, it's not about competing and I've already said I'm going to ignore you in the future, even when your behaviour continues to irritate me.

Pablo Beltran
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
February 28, 2016

So, what did you not vote -1 for the Udo's answer which is not related to ScriptRunner and he provides a totally different approach?  Today, you voted me negative for more than ten times and other days a lot more times. You have also posted "stop spam"  to my answers, users have been fooled by you saying that my plugin did not do what it really does, etc, etc, etc. 

honestly, I'M VERY VERY TIRED!

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
February 28, 2016

Because Udo's answer was also a useful one that involved script runner.

Please re-read my last comment and move on.

Pablo Beltran
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
February 28, 2016

And SQL for JIRA too. If you read the documentation it supports a public API for third party add-ons which can be invoked from ScritRunner to do things much easier and in a more powerful way. Much more than a custom field as Udo suggests. And for sure you have good reasons too for the +30 times you voted me negative and tried to discredit ALL my other answers. It is difficult for anyone to think this is not a personal issue for you against SQL for JIRA.... I did not understand yet why you have tried to damage the reputation of this plugin in this way...

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
February 28, 2016

Please re-read the previous comments.  Until you've started reading them, I see no point in engaging with you any further.

Pablo Beltran
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
February 28, 2016

I would start reading them from the beginning, but you have deleted them... Oh!

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
February 28, 2016

Yes, I did delete some, as airing this in public is not useful.  I'd suggest we agree to delete the rest, but won't do it just yet.

It's the last few that you need to read instead of ignore.

Suggest an answer

Log in or Sign up to answer