How Can I Find an Issue’s Fix Version?

Question

I can find rows with field_id=fixVersions in the ISSUE_MULTISELECT_HISTORY table. However, the corresponding value column provides a numeric ID instead of an applicable Jira fix version. How can I find an issue's fix version?

Environment

Connector: Jira

Answer

To find a Jira issue's fix version, do the following:

1. In your destination, find the ISSUE_MULTISELECT_HISTORY table.
2. Locate rows where field_id=fixVersions.
3. Locate the corresponding value row.
4. Note the ID provided in the value row.
5. Go to the VERSION table.
6. Locate the ID you found in step 4 in the ID column.
7. Find the corresponding name column.

The name column in the VERSION table provides the name of the Jira fix version associated with the ID provided in the value row of the ISSUE_MULTISELECT_HISTORY table.

You can also join the ISSUE_MULTISELECT_HISTORY and VERSION tables to get the fix version using the following database query:

select i.id, imh.field_id, imh.issue_id, v.name
from JIRA.ISSUE i
inner join JIRA.issue_multiselect_history imh 
ON imh.ISSUE_ID = i.id
left outer join JIRA.VERSION v
   on v.project_id = i.project
   and TO_CHAR(v.id) = TRIM(imh.VALUE)
   and is_active=true
where
 imh.field_id='fixVersions';