When i use KEY variable a get string with full key issue 'BFD-1234'
I need to have only issue numer to put a parameter in SQL query. It's possible to do in dedicated metohod or I need to use replace function ?
There is no dedicated SIL routine for retrieving only the number part of an issue key.
However, you can use the string replace routine, as you mentioned:
replace(key, project + "-", "")
or the substring routine:
substring(key, indexOf(key, "-") + 1, length(key))
Issue numbers (the bit after the - in the key) are NOT unique, so it's pretty much pointless using them. You should be using the full Key, or you could get duplicates.
If you really are looking to get multiple issues with the same numbers in their key, then yes, you'll need to manipulate the data to split off the project key before using the number.
But that's the point - if you select on just the issue-key numerical part, you can get several results. Issues ABC-123, XYZ-123 and JRA-123 if you selected for "123"
You don't need to join to the project table either - the key already contains the project short-code.
I'm actually quite curious as to why you want this - I can't think of any real use for it. Unique issues, yes. Exposing the database ID to use that instead, yes (and I've written code to help with that). But selecting potentially multiple issues on the numerical part of the issue key seems pretty pointless. I suspect it's just my imagination letting me down. Indulge an old hacker, I'd be really grateful if you could explain why you're doing this?
Since JIRA 6.x jiraissue table don't store pkey column - it's always null. So You can't query like this:
SELECT ID FROM jiraissue WHERE pkey = '<all string KEY>'
You need query like this:
SELECT ID FROM jiraissue WHERE issuenum = <number part of KEY> AND project = (SELECT PROJECT_ID FROM project_key WHERE PROJECT_KEY = '<string part of KEY>')
to get ID of only one issue.
"I'm actually quite curious as to why you want this " - I want this to get array with username who votes in issue. To do that I need ID from issue (SQL table ID).
So whole my code looks like this:
SELECT [SOURCE_NAME] FROM [userassociation] WHERE ASSOCIATION_TYPE = 'voteIssue' AND SINK_NODE_ID = (SELECT ID FROM jiraissue WHERE issuenum = <number part of KEY> AND project = (SELECT PROJECT_ID FROM project_key WHERE PROJECT_KEY = '<string part of KEY>'))
Atlassian Summit is an excellent opportunity for in-person support, training, and networking.Learn more
One of the newest products in the Microsoft family - Microsoft Teams, is a chat-based hub for teamwork that integrates all the people, content, and tools your team needs to be more engaged and ...
Connect with like-minded Atlassian users at free events near you!Find a group
Connect with like-minded Atlassian users at free events near you!
Unfortunately there are no AUG chapters near you at the moment.Start an AUG
You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs