Skip to content

Instantly share code, notes, and snippets.

@stelf

stelf/jira.identifiers.keycloud.sql

Created June 13, 2022 13:45
Show Gist options
  • Save stelf/83cdffdef4d6362679fabae51dde6cf6 to your computer and use it in GitHub Desktop.
Save stelf/83cdffdef4d6362679fabae51dde6cf6 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. stelf revised this gist Jun 13, 2022. No changes.
  2. stelf created this gist Jun 13, 2022.
    24 changes: 24 additions & 0 deletions jira.identifiers.keycloud.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,24 @@
    SELECT distinct
    upper(CAST (keywords.str AS varchar2(32))) AS keyword,
    JP.pkey || '-' || JI.issuenum AS issuekey
    FROM
    JIRAACTION jA
    JOIN jiraissue JI ON
    JA.issueid = JI.id
    JOIN project JP ON
    JI.project = JP.id
    CROSS JOIN lateral
    (SELECT
    regexp_substr(ja.actionbody, '(^|\s)(\w+_\w+)($|\s)', 1, LEVEL, NULL, 2) str
    FROM
    dual
    CONNECT BY
    LEVEL <= regexp_count(ja.actionbody,'(^|\s)\w+_\w+($|\s)')
    ORDER BY LEVEL ASC
    ) keywords
    WHERE
    JA.actiontype = 'comment'
    AND REGEXP_LIKE(JA.actionbody, '(^|\s)\w+_\w+($|\s)')
    AND NOT regexp_like(keywords.str, '^_+$')
    -- AND rownum < 400
    ORDER BY issuekey