Table Transformer Macro - JOIN query

[Jignesh Patel]

I have one table (by Excerpt Include macro) inside a Table Transformer macro with following columns

DeptId, ManagerId, EmployeeId, Salary

I want to show total salary by DeptId and ManagerId
But
I want to order the records in descending order of total salary by department

Select TA.*
FROM
           (Select T1.'DeptId', T1.'ManagerId', SUM(T1.'Salary') 'Dept-Manager Salary'
            FROM T1
            GROUP BY T1.'DeptId', T1.'ManagerId') AS TA
     JOIN
           (Select T1.'DeptId', SUM(T1.'Salary') 'Dept Salary'
            FROM T1
            GROUP BY T1.'DeptId') AS TB
       ON TA.'DeptId' = TB.'DeptId'
ORDER BY
        TB.'Dept Salary' DESC

Why is this not working as expected?

[Katerina Kovriga {Stiltsoft} · Accepted solution]

Hi @Jignesh Patel ,

The JOIN function can be unstable working with several internal SELECTs. We have this improvement on our roadmap, I've linked this question to the ticket to notify you later.

As for now you may use the following structure (don't forget to use the Table Toolbox macro to nest macros if your hosting is Cloud):

We just split your original query into three separate queries.

For TT1:

Select T1.'DeptId', T1.'ManagerId', SUM(T1.'Salary') 'Dept-Manager Salary'
FROM T1
GROUP BY T1.'DeptId', T1.'ManagerId'

For TT2:

Select T1.'DeptId', SUM(T1.'Salary') 'Dept Salary'
FROM T1
GROUP BY T1.'DeptId'

For external TT:

SELECT T1.'DeptId', T2.'Dept Salary', T1.'ManagerId', T1.'Dept-Manager Salary'
FROM T1 JOIN T* ON T1.'DeptId' = T*.'DeptId'
ORDER BY T2.'Dept Salary' DESC

Then the result table will be as following:

Hope this workaround may help.

[Jignesh Patel]

Thanks for your quick response, @Katerina Kovriga {Stiltsoft} 
This workaround works for us for now.

Would like to hear when JOIN function is updated as you mentioned.

Thanks a lot, again!

[Alexey Mikhaylov _Stiltsoft_]

Hello @Jignesh Patel ,

Let me inform you we have resolved the issue of LEFT/OUTER JOIN functioning incorrectly with the nested SELECT in Table Transformer in version 10.1.0 of Table Filter and Charts for Confluence.