Need SQL for a JQL query

[Sarah Shonnard]

I have the following Issue Filter in JQL which gives me my backlog by rank order:

project = XXX and (type = Story or type = bug) and status != Closed order by rank asc

I'd like to have an equivilant SQL query so I can pull the same data directly in to an Excel spreadsheet. I know "Rank" is a linked list which requires some sort of a recursive SQL query.

Can anybody provide and example so I don't have to wrack my brain trying to figure this out myself.

[Peter Van de Voorde]

This will give you a list of all the ids of the requested jiraissues and their current ranking:

select i.id,r.id 
from 
jiraissue i,
AO_60DB71_ISSUERANKING r,
project p 
where 
i.id=r.issue_id 
and i.PROJECT=p.id 
and p.PKEY = 'JC' 
and i.ISSUESTATUS not in (select id from ISSUESTATUS where pname in ('Closed')) 
and i.ISSUETYPE not in (select id from ISSUETYPE where PNAME in ('Story','Bug'))
order by r.id;

Just change the 'JC' to your own project key.

[Udo Brand]

should be

... and i.ISSUETYPE in (select ...

(remove the not)

since you want Story and Bug

[Sarah Shonnard]

While this SQL works, it's not what I need. I'm not sure what it's giving me, but the list I get back doesn't match the backlog in Greenhopper. I thought since RANK is a linked list you couldn't just order by RANK.id?

[Pablo Beltran]

SQL for JIRA does the job. Simply perform a query similar to this:

select * 
from issues 
where JQL='project = XXX and (type = Story or type = bug) and status != Closed order by rank asc'

Simple? 

Of course, you have to read the plug-in documentation in order to learn hot to export to a CSV format, but it is pretty easy too! 

[Sarah Shonnard]

That's what I'm currently doing. However, as I make updates in JIRA it's a pain to have to export every time. I'd rather have real time access to my changes by being able to run a query against the backend database directly.

[Udo Brand]

Why don't you just export your JQL results in Excel?