i would like to have a SQL query wherein I can get the epic name of a story.
The database architecture 'n schema appears to be more confusing.This doesn't even describe much of details (https://developer.atlassian.com/jiradev/jira-platform/jira-architecture/database-schema#Databaseschema-Customfields)
Likewise - I do have tables customfield and customfieldvalue from where I can have the required 'stringvalue' as epic name but id and issue column makes more confusion as customfieldvalue.id is from nowhere to generate?
Run this query and replace the 10111 value to the relevant custom field value of your JIRA instance:
select p.pkey, ji.issuenum, cfv.STRINGVALUE from jiraissue ji join project p on ji.PROJECT = p.ID join customfieldvalue cfv on ji.ID = cfv.ISSUE where cfv.CUSTOMFIELD = 10111;
Output will contain the issue key and the custom field value.
@Noam Dahan thanks that works - but it returns epic issuetype list. What if I want to pass a pkey and issuenum=1836 (1836 will be key for story issuetype)?
select p.pkey, ji.issuenum, cfv.STRINGVALUE,ist.pname from jiraissue ji join project p on ji.PROJECT = p.ID join customfieldvalue cfv on ji.ID = cfv.ISSUE join issuetype ist on ji.issuetype = ist.id where cfv.CUSTOMFIELD = 10004 and p.pkey='ATPCWS' and ji.issuenum=1836
Query you provided worked; but output was only with EPIC issue type - wherein I was looking for a STORY ID to be passed and get the epic link 'name' (thats stringvalue). Your code really helped
I ended up creating the below one:
select ji.issuenum AS Epic_Key, cfv.STRINGVALUE AS Epic_Name from jiraissue ji join customfieldvalue cfv on ji.ID = cfv.ISSUE join issuetype ist on ji.issuetype = ist.id where cfv.CUSTOMFIELD = 10004 and ji.issuenum=( select issuenum from jiraissue where id=( select islk.source from jiraissue ji join issuelink islk on ji.id=islk.source join issuelinktype ilt on islk.linktype=ilt.id join jiraissue c on islk.destination = c.id where ilt.linkname='Epic-Story Link' and c.id=(select jre.id from jiraissue jre, project prj where jre.project=prj.id and prj.pkey='ATPCWS' and jre.issuenum=1836)) )
SELECT p.pkey || '-' || ji.issuenum AS issueid,cfv.stringvalue AS epicname FROM jiraissue ji LEFT JOIN project p ON p.id=ji.project LEFT JOIN customfieldvalue cfv ON cfv.customfield=(SELECT id FROM customfield WHERE cfname LIKE 'Epic Name') AND cfv.issue=ji.id WHERE ji.issuetype=(SELECT id FROM issuetype WHERE pname LIKE 'Epic') AND (cfv.stringvalue IS NULL OR cfv.stringvalue = '') ORDER BY p.pkey, ji.issuenum ;
Although this should never happen as the Epic Name field is required.
If you spend enough time as a Jira admin - whether you are managing a single, mid-sized instance, a large enterprise one or juggling multiple instances at once - you will eventually find yourself in ...
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!
Unfortunately there are no AUG chapters near you at the moment.Start an AUG
We're bringing product updates and pro tips on teamwork to ten cities around the world.Save your spot