Hi!
It's very important for me get the issue type scheme associated of all projects in database. I dont find the way to get it. I don't know wich query I should execute.
Plese, help me.
Thanks so much!
Thanks Marcus, but i need all the projects that doesnt have the default issue type scheme.
I think the correct query is:
select project.pname, pkey from project where project.ID not in ( select project.ID from project join configurationcontext on project.ID = configurationcontext.PROJECT where configurationcontext.customfield = "issuetype" and configurationcontext.FIELDCONFIGSCHEME != 10000 and configurationcontext.FIELDCONFIGSCHEME is not null)
Thanks Marcus!
Hi David,
This should return all Issue Types associated to the project "Test".
select IT.pname from project P join configurationcontext CC on P.id = CC. project join fieldconfigschemeissuetype FCI on FCI.fieldconfigscheme = CC.fieldconfigscheme join optionconfiguration OC on OC.fieldconfig = FCI.fieldconfiguration join issuetype IT on IT.id = OC.optionid where P.pname = 'Test'
Hope this helps
I thought you needed all issues types associated to a specific project.
My bad
Your query does seem to get the expected results.
If you need any help, just let me know.
Able to get the results as expected.
Thank you.
Can you please help to get a list of projects which has issuetype 'Bug'?
Hi,
I was dealing this too and here is my query (MySQL).
This returns list of all projects (key, name), associated issue type scheme and list of all issue types.
Adjust this query accordingly based on your needs.
SELECT p.pkey AS 'Project Key', p.pname AS 'Project Name', its.configname AS 'Issue Type Scheme', its.issuetypes AS 'Issue Types'FROM project pLEFT JOIN configurationcontext cc ON p.ID = cc.PROJECTLEFT JOIN ( SELECT fcsit.FIELDCONFIGSCHEME, fcsit.FIELDCONFIGURATION, fcs.configname, GROUP_CONCAT(it.pname ORDER BY oc.SEQUENCE SEPARATOR ', ') AS issuetypes FROM optionconfiguration oc LEFT JOIN issuetype it ON it.ID = oc.OPTIONID LEFT JOIN fieldconfigschemeissuetype fcsit ON fcsit.FIELDCONFIGURATION = oc.FIELDCONFIG LEFT JOIN fieldconfigscheme fcs ON fcs.ID = fcsit.FIELDCONFIGSCHEME GROUP BY oc.FIELDCONFIG) its ON its.FIELDCONFIGSCHEME = cc.FIELDCONFIGSCHEMEWHERE cc.customfield = 'issuetype'-- HAVING its.issuetypes RLIKE 'Support Ticket';
Using the last "HAVING" statement and RLIKE or NOT RLIKE you can filter for various issue types.
Works Great!
Thank you
The following SQL (SQL Server) will retrieve the name of the Issue Types Scheme used on project(s):
SELECT prj.pkey as 'Project Key', prj.pname as 'Project Name',(CASE -- get the Issue Types schemeWHEN EXISTS(select cc.FIELDCONFIGSCHEME from [configurationcontext] cc where cc.customfield = 'issuetype' AND cc.PROJECT = prj.ID) THEN(select top 1 fc.configname from [fieldconfigscheme] fcwhere fc.ID = ( select top 1 cc.FIELDCONFIGSCHEME from [configurationcontext] ccwhere cc.customfield = 'issuetype' AND cc.PROJECT = prj.ID) )ELSE(select fc.configname from [fieldconfigscheme] fcwhere fc.ID = 10000) -- Default Issue Type SchemeEND) as 'Issue Types Scheme Name'FROM [project] prjWHERE prj.pkey = 'your desired project key'
@Radek Janata Hi
I get below error, any idea how can this be addressed?
[42000][1055] Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'jiradb.fcsit.FIELDCONFIGSCHEME' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
@Grzegorz Ziolek
What error do you mean? Nothing was attached.
You probably run your Jira on different DB than MySQL. You might need to change some parts to work it on different DB.
It looks like you're new here. Sign in or register to get started.
