One thing that makes me very scary every time i hear about it is, that COVID-19 looks like a JIRA ticket. To demonstrate what i mean i just made a new project with COVID project prefix.
A string, date or a text-value of an custom field shall be retrieved from an issue
SELECT pk.PROJECT_KEY+'-'+CAST(ji.issuenum AS varchar(max)) as issue,
cfv.STRINGVALUE,
cfv.TEXTVALUE,
cfv.DATEVALUE
FROM jiraissue ji
INNER JOIN customfieldvalue cfv ON ji.id=cfv.issue
INNER JOIN project_key pk ON ji.PROJECT=pk.PROJECT_ID
WHERE pk.PROJECT_KEY+'-'+CAST(ji.issuenum AS varchar(max))='AT-15'
AND cfv.CUSTOMFIELD=(SELECT TOP 1 id FROM customfield WHERE cfname='Requested document')
To fetch the values „Affects version“ or „Fixed Version“ from the JIRA Database via SQL, the use of different relations/tables is necessary
You can use the ASSOCIATION_TYPE=’IssueVersion‘ for Affects version and ASSOCIATION_TYPE=’IssueFixVersion‘ to get the Fixed version
The next example shows how to retrieve Affects Version for Issue-ID AT-15:
SELECT projectversion.id, vname, project_key.PROJECT_KEY+'-'+CAST(jiraissue.issuenum AS varchar(max)) as pkey, ASSOCIATION_TYPE
FROM projectversion, nodeassociation, jiraissue, project_key
WHERE SINK_NODE_ID = projectversion.id
AND SOURCE_NODE_ID = jiraissue.id
AND project_key.PROJECT_ID=jiraissue.PROJECT
AND ASSOCIATION_TYPE='IssueVersion'
AND project_key.PROJECT_KEY+'-'+CAST(jiraissue.issuenum AS varchar(max))='AT-15'
The version strings of custom field, that can select multiple versions, shall be selected
The table projectversion can be used to connect to CustomFieldValue.NUMBERVALUE
SELECT pk.PROJECT_KEY+'-'+CAST(a.issuenum AS varchar(max)) as issue, pv.vname FROM project_key pk INNER JOIN jiraissue a ON pk.PROJECT_ID=a.PROJECT INNER JOIN CustomFieldValue b ON a.ID=b.ISSUE INNER JOIN customfield c ON b.CUSTOMFIELD=c.id INNER JOIN projectversion pv ON pv.ID=b.NUMBERVALUE WHERE c.CFName = 'LUA Version(s)' --AND pk.PROJECT_KEY+'-'+CAST(a.issuenum AS varchar(max)) ='NDS-4352' ORDER BY 1
Für alle JIRA Projekte soll die Anzahl der Issues pro Issue Type und Issue Status ermittelt werden.
SQL Database Query
SELECT project.pname 'Project', issuetype.pname 'Issue Type', issuestatus.pname 'Status', COUNT(project_key.PROJECT_KEY+'-'+CAST(issuenum AS VARCHAR(5))) 'Count' FROM jiraissue INNER JOIN project_key ON jiraissue.PROJECT=project_key.PROJECT_ID INNER JOIN project ON CAST(project.ID as nvarchar)=project_key.PROJECT_ID INNER JOIN issuestatus ON issuestatus.ID=jiraissue.issuestatus INNER JOIN issuetype ON issuetype.ID=jiraissue.issuetype GROUP BY project.pname, issuetype.pname, issuestatus.pname
A selected custom field value, that is in a MS SQL Server JIRA Database should be retrieved.
The tables project_key, jiraissue, CustomFieldValue, customfield and customfieldoption have to be joined
SELECT pk.PROJECT_KEY+'-'+CAST(a.issuenum AS varchar(max)) as issue, c.cfname as field, d.customvalue FROM project_key pk INNER JOIN jiraissue a ON pk.PROJECT_ID=a.PROJECT INNER JOIN CustomFieldValue b ON a.ID=b.ISSUE INNER JOIN customfield c ON b.CUSTOMFIELD=c.id INNER JOIN customfieldoption d ON c.id=d.CUSTOMFIELD WHERE c.CFName = 'Requirements/Specifications affected?' AND b.STRINGVALUE=CAST(d.id as varchar(max))
The actual value of a custom field from an Issue should be selected
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;
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.
Analysing the table shows a new column name „issuenum“ which can be used in combination with the colum „project“ to concatenate the Issue Key.
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‘