Let's say i have:
Let's also say I'm not super confident in my sql skills (my jql is way better). Since project A is the only project using this importance field, I'm doing something like:
SELECT `Customfieldoption`.`CUSTOMFIELD` AS `CUSTOMFIELD`, `Customfieldoption`.`customvalue` AS `Customvalue` FROM `customfieldoption` AS `Customfieldoption` WHERE ((`Customfieldoption`.`CUSTOMFIELD` = 12321))
and what I end up with is three rows for each issue--it's showing me ALL OPTIONS rather than the selected option. Is this:
SQL is absolutely the best way to get JIRA data :) You just need to know a few table facts:
To learn the JIRA tables behind a particular operation, a useful trick is to generate a text dump of the database before the operation (e.g. editing a customfield) and after, then diff the two text dumps. See https://www.redradishtech.com/display/KB/Using+database+diffs+to+see+what+JIRA+is+doing
I think this is what I'm looking for. Let me try it first and then I'll approve this answer. Thanks in advance, Jeff!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Jeff, I think the complexity of your answer to get a really simple basic thing proves the point - SQL is by far the worst way to do reporting on JIRA.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Depends on the context of what you're doing, but there's two basic ones.
If you're working inside JIRA, in an add-on, then the API has several simple "give me custom field value from issue X" options.
From outside JIRA, a single REST call fetches the whole issue in one step (and if you make it a bit longer, you can ask for a single field)
SQL is an utter disaster for reporting in JIRA. Don't do it.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
select o."NAME", concat(pro.pkey,'-',ji.issuenum) from "AO_54307E_ORGANIZATION" o
join "AO_54307E_ORGANIZATION_MEMBER" om on om."ORGANIZATION_ID" = o."ID"
join jiraissue ji on ji.reporter = om."USER_KEY"
join project pro on pro.id = ji.project
where o."NAME" = 'Organization name';
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
SQL is absolutely the worst way to get to JIRA data, and should not be used in this way. As a simple example, you now need to join over 40 tables (many of them more than once) just to get the data for a single issue. Do not use SQL to do it.
(And, please, when posting questions, drop the use of "as", it does nothing more than save you a bit of typing while making your SQL a lot harder to read)
So, moving on, you don't say what question you are trying to answer with this query. What are you looking for here?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Apologies for the illegibility of my sql. As noted, sql is not one of my strong points.
At the micro level, I'm looking for a list of project A issues with their corresponding "importance" value. At a higher level, I'm hoping to use the query in Chartio to present a stacked bar chart of issues created monthly, the bar stacks representing "importance."
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.