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

[GB]

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

[Stiltsoft support]

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.