It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

How to get all Issue-IDs with a particular custom field filled?

Hans-Hermann Hunfeld Oct 17, 2013

As we re going to update an external system with the according JIRA key (and afterwards get the vice-versa update updating the JIRA issues) we have to get a list with all issues which have a value for

select * from customfield where CFNAME = 'HP OpenView Type' OR CFNAME ='HP OpenView ID';

The export should contains these both CFNAME fields (combined primary key) and the according JIRA issue ID.

The final query will directly run against the JIRA DB, the import job from external will be done manually first.

4 answers

1 accepted

0 votes
Answer accepted
Hans-Hermann Hunfeld Oct 23, 2013
SELECT   p.pkey || '-' || ji.issuenum "JIRA-Key", CV.*
    FROM jiraissue ji,
         (SELECT nvl(cv1.issue,cv2.issue) AS "ISSUE", cv1.stringvalue AS "HP_OpenView_Type", cv2.numbervalue AS "HP_OpenView_ID"
            FROM (SELECT *
                    FROM customfieldvalue
                   WHERE customfield = 11602) cv1
                 inner JOIN
                 (SELECT *
                    FROM customfieldvalue
                   WHERE customfield = 11601) cv2 ON cv1.issue = cv2.issue
                 ) CV,
         project p
   WHERE ji.ID = CV.issue AND ji.project = p.ID

0 votes
Florin Manaila Oct 17, 2013

The customfieldvalue table holds the values for a certain issue (you have FK on issue and the custom fields from the SELECT you mentioned), so you can query that.

0 votes
Nic Brough [Adaptavist] Community Leader Oct 17, 2013

You need to be reading customfieldvalue for the data value. That table has all of the custom field data, so you will need to limit the selection with the issue id(s) (from reading jiraissue), and the custom field id(s), which you get from your SQL already posted.

When you are writing data from external to Jira, remember that you can NOT do it with SQL.

0 votes
Hans-Hermann Hunfeld Oct 17, 2013

Hi all,

thanks for your reply!
Meanwhile i found the db scheme where i tried to create an according query. As soon as we got some results i ll post it here...

Thanks & regards,

Hans-Hermann

Suggest an answer

Log in or Sign up to answer
This widget could not be displayed.
This widget could not be displayed.
Community showcase
Published in Next-gen

Introducing subtasks for breaking down work in next-gen projects

Teams break work down in order to help simplify complex tasks. This is often done iteratively, with tasks being broken down into smaller tasks and so on until the work is accurately captured in well-...

9,998 views 46 44
Read article

Community Events

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

Find an event

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

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you