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.
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
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.
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