Problem

The actual value of a custom field from an Issue should be selected

Solution

select CAST(pk.PROJECT_KEY as VARCHAR(10))+'-'+CAST(issue.issuenum AS varchar(10)), cfo.customvalue FROM 
CustomFieldValue cfv 
INNER JOIN CustomField CF on CF.Id = CFV.CustomField
INNER JOIN CustomFieldOption CFO on CF.Id  = CFO.CustomField
INNER JOIN jiraissue issue on issue.id = cfv.issue 
INNER JOIN project_key pk on PROJECT_ID=issue.PROJECT
WHERE cf.CFName = 'Classification' 
And CAST(CFO.Id AS VARCHAR(10)) =CFV.StringValue;

Problem

In prior versions than JIRA 6.1., the table „jiraissue“ in the database has stored the Issue IDs and has made it possible to make a selection on Issue IDs. Unfortunalety this field is empty now. It is not possible to select values because of the Issue ID.

Approach

Analysing the table shows a new column name „issuenum“ which can be used in combination with the colum „project“ to concatenate the Issue Key.

Solution

SELECT project_key.PROJECT_KEY+‘-‚+CAST(issuenum AS VARCHAR(5)) as pkey, jiraissue.*
FROM jiraissue
INNER JOIN project_key
ON jiraissue.PROJECT=project_key.PROJECT_ID
WHERE project_key.PROJECT_KEY+‘-‚+CAST(issuenum AS VARCHAR(5))=’VVTT-65‘