Forums

Articles
Create
cancel
Showing results for 
Search instead for 
Did you mean: 

Table Transformer sql | Merging Jira Issue tables by linked issues, parent link and Epic link

GB
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
February 22, 2023

I need to transform 10 Jira tables to one table considering the links( Linked Issues, Parent Link and Epic Link). So that in one row the dependency and status of the tickets are reflected.

T1 = Portfolio-Epic 

T2 and T4 = Epic (Parent Link -> T1)

T3 = Story (Epic Link -> T2)

T5, T6, T7, T8, T9 and T10 = Story (issueLinkType 'relates to' -> T2 or T4)

Dependency between Epic and Portfolio Epic via Parent Link is displayed correctly.

FROM T1
LEFT OUTER JOIN T2 ON T1.'Key' = T2.'Parent Link'
OUTER JOIN T4 ON T1.'Key' = T4.'Parent Link'

Dependency between Story and Epic via Epic Link is displayed correctly.

OUTER JOIN T3 ON T2.'Summary' = T3.'Epic Link'

Dependency between Story and Epic via Linked Issues is not always displayed correctly (only some tickets but not all ).

OUTER JOIN T5 ON T2.'Linked Issues'->split(", ")->indexOf(T5.'Key'::string) > -1
OUTER JOIN T6 ON T2.'Linked Issues'->split(", ")->indexOf(T6.'Key'::string) > -1
OUTER JOIN T7 ON T4.'Linked Issues'->split(", ")->indexOf(T7.'Key'::string) > -1
OUTER JOIN T8 ON T4.'Linked Issues'->split(", ")->indexOf(T8.'Key'::string) > -1
OUTER JOIN T9 ON T4.'Linked Issues'->split(", ")->indexOf(T9.'Key'::string) > -1
OUTER JOIN T10 ON T4.'Linked Issues'->split(", ")->indexOf(T10.'Key'::string) > -1

Can you please help me to find the error from my SQL?

Total SQL query:

SELECT
T1.'Customer Cluster' as 'Customer Cluster',
T1.'Key' as 'Key_',
T1.'Summary' as 'Summary',
FORMATWIKI("{cell:bgColor=#FAFAFA}" + " " + "{cell}") as 'A',
T2.'Key' as 'Key',
T2.'Status' as 'Status',
FORMATWIKI("{cell:bgColor=#FAFAFA}" + " " + "{cell}") as 'B',
T3.'Key' as 'Key__',
T3.'Status' as 'StatusI',
FORMATWIKI("{cell:bgColor=#FAFAFA}" + " " + "{cell}") as 'C',
T4.'Key' as 'KeyI',
T4.'Status' as 'StatusII',
FORMATWIKI("{cell:bgColor=#FAFAFA}" + " " + "{cell}") as 'D',
T5.'Key' as 'KeyII',
T5.'Status' as 'StatusIII',
FORMATWIKI("{cell:bgColor=#FAFAFA}" + " " + "{cell}") as 'F',
T6.'Key' as 'KeyIII',
T6.'Status' as 'StatusIV',
FORMATWIKI("{cell:bgColor=#FAFAFA}" + " " + "{cell}") as 'G',
T7.'Key' as 'KeyIV',
T7.'Status' as 'StatusV',
FORMATWIKI("{cell:bgColor=#FAFAFA}" + " " + "{cell}") as 'H',
T8.'Key' as 'KeyV',
T8.'Status' as 'StatusVI',
FORMATWIKI("{cell:bgColor=#FAFAFA}" + " " + "{cell}") as 'I',
T9.'Key' as 'KeyVI',
T9.'Status' as 'StatusVII',
FORMATWIKI("{cell:bgColor=#FAFAFA}" + " " + "{cell}") as 'J',
T10.'Key' as 'KeyVII',
T10.'Status' as 'StatusVIII'

FROM T1
LEFT OUTER JOIN T2 ON T1.'Key' = T2.'Parent Link'
OUTER JOIN T3 ON T2.'Summary' = T3.'Epic Link'
OUTER JOIN T4 ON T1.'Key' = T4.'Parent Link'
OUTER JOIN T5 ON T2.'Linked Issues'->split(", ")->indexOf(T5.'Key'::string) > -1
OUTER JOIN T6 ON T2.'Linked Issues'->split(", ")->indexOf(T6.'Key'::string) > -1
OUTER JOIN T7 ON T4.'Linked Issues'->split(", ")->indexOf(T7.'Key'::string) > -1
OUTER JOIN T8 ON T4.'Linked Issues'->split(", ")->indexOf(T8.'Key'::string) > -1
OUTER JOIN T9 ON T4.'Linked Issues'->split(", ")->indexOf(T9.'Key'::string) > -1
OUTER JOIN T10 ON T4.'Linked Issues'->split(", ")->indexOf(T10.'Key'::string) > -1

1 answer

2 votes
Stiltsoft support
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.
February 23, 2023

Hi @GB ,

Please create a support ticket here (our portal is based on the Jira Service Desk and it's confidential).

Copy the description of the case from this question and attach screenshots of your Jira Issues macros in a published state (without any other macros, with visible headers and 2-3 first rows of data). We'll recreate exactly the same tables and look into the issue. 

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
CLOUD
TAGS
AUG Leaders

Atlassian Community Events