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}

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.