Are you in the loop? Keep up with the latest by making sure you're subscribed to Community Announcements. Just click Watch and select Articles.

Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

Earn badges and make progress

You're on your way to the next level! Join the Kudos program to earn points and save your progress.

Deleted user Avatar
Deleted user

Level 1: Seed

25 / 150 points

Next: Root


1 badge earned


Participate in fun challenges

Challenges come and go, but your rewards stay with you. Do more to earn more!


Gift kudos to your peers

What goes around comes around! Share the love by gifting kudos to your peers.


Rise up in the ranks

Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!


Table Transformer Macro - JOIN query

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
I want to order the records in descending order of total salary by department

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

Why is this not working as expected?

1 answer

1 accepted

4 votes
Answer accepted
Katerina Kovriga _Stiltsoft_
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.
Sep 16, 2022

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):

Fri 8-1.png

We just split your original query into three separate queries.

For TT1:

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

For TT2:

Select T1.'DeptId', SUM(T1.'Salary') 'Dept Salary'
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:

Fri 8-2.png

Hope this workaround may help.

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!

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.

Like Stiltsoft support likes this

Suggest an answer

Log in or Sign up to answer
AUG Leaders

Atlassian Community Events