How to add in SQL quer customfield (category)

NewByatl
Contributor
June 16, 2023

How to add in SQL select extra column (customfield (category))? Maybe someone has some kind of sql script?

Now it looks like this:


select ASSIGNEE, SUMMARY, concat(originalkey, '-',issuenum) as Issuekey ,issuetype
from jiraissue, project
where project.id = jiraissue.project
and project.pkey = 'ABC'

2 answers

0 votes
Nic Brough -Adaptavist-
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
June 17, 2023

Welcome to the Atlassian Community!

It does not really work that way.  Your best bet is to stop using SQL to query the database (it really is not designed for reporting, you can cripple your DB with the most innocuous-looking request and it often changes on upgrade)

What problem are you trying to solve here?  What is the report you are trying to write?

NewByatl
Contributor
June 18, 2023

Hello! @Nic Brough -Adaptavist- 

I'm trying to write something like that:

It looks like JQL report: project = "ABC"

And with extra column Category. If I use query like below, it returns me null in category column ( but It can't be, because  all category fields are filled in)

category.PNG

 


select ASSIGNEE, SUMMARY, concat(originalkey, '-',issuenum) as Issuekey ,issuetype,

(SELECT cfo.customvalue
FROM customfield cf, customfieldvalue cfv , customfieldoption cfo
where cf.id = cfv.customfield
and cast(cfv.stringvalue as integer) = cfo.id
and cf.ID = '24411' AND jiraissue.id = cfv.customfield
) AS Categoryy


--select*
from jiraissue, project
where project.id = jiraissue.project
and project.pkey = 'ABC'

Nic Brough -Adaptavist-
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
June 18, 2023

I suggest you use JQL and the issue navigator to do this.

NewByatl
Contributor
June 18, 2023

@Nic Brough -Adaptavist- 

Yea! I can do this, but I wanna get the same report via SQL, because I want to export more than 1000 issues (90293 issues) and ..I wanna find out how to export it via SQL (kind of challenge)

0 votes
NewByatl
Contributor
June 17, 2023

Now it looks like this (but still null in Categoryy column ) :

 

select ASSIGNEE, SUMMARY, concat(originalkey, '-',issuenum) as Issuekey ,issuetype,

(SELECT cfo.customvalue
FROM customfield cf, customfieldvalue cfv , customfieldoption cfo
where cf.id = cfv.customfield
and cast(cfv.stringvalue as integer) = cfo.id
and cf.ID = '24411' AND jiraissue.id = cfv.customfield
) AS Categoryy


--select*
from jiraissue, project
where project.id = jiraissue.project
and project.pkey = 'ABC'

Suggest an answer

Log in or Sign up to answer