Several SQL-queries in a single table transformer

Alexandra Kühn_puzlnw

Hello,

I have 6 table transformers that use the same table extract with the following SQL query:

SELECT 'department', COUNT(DISTINCT('title')) AS 'publications' FROM T* GROUP BY 'department'

SELECT 'department', COUNT(DISTINCT('title')) AS 'refereed' FROM T* WHERE 'refereed' LIKE "%TRUE%" GROUP BY 'department'

SELECT 'department', COUNT(DISTINCT('title')) AS 'isi' FROM T* WHERE 'isi' LIKE "%TRUE%" GROUP BY 'department'

SELECT 'department', COUNT(DISTINCT('title')) AS 'scopus' FROM T* WHERE 'scopus' LIKE "%TRUE%" GROUP BY 'department'

SELECT 'department', COUNT(DISTINCT('title')) AS 'Bachelor' FROM T* WHERE 'thesis_type' LIKE "%bachelor%" GROUP BY 'department'

SELECT 'department', COUNT(DISTINCT('title')) AS 'Master' FROM T* WHERE 'thesis_type' LIKE "%master%" GROUP BY 'department'

These differ mainly in the where clause. I then combine the 6 table transformers into a single table.

SELECT * FROM T1 OUTER JOIN T* ON T1.'department' = T*.'department'

Can I do this somehow in a single table transformer?

 

Stiltsoft support

Hi @Alexandra Kühn ,

I've consulted with our developers and they provided me with a following solution: use several JOINs subsequently one by one.

SELECT * FROM
(SELECT 'department', COUNT(DISTINCT('title')) AS 'publications' FROM T1 GROUP BY 'department')
AS T1_1 OUTER JOIN (
SELECT 'department', COUNT(DISTINCT('title')) AS 'refereed' FROM T1 WHERE 'refereed' LIKE "%TRUE%" GROUP BY 'department'
) AS T1_2 ON T1_1.'department' = T1_2.'department'
OUTER JOIN (
SELECT 'department', COUNT(DISTINCT('title')) AS 'isi' FROM T1 WHERE 'isi' LIKE "%TRUE%" GROUP BY 'department'
) AS T1_3 ON T1_1.'department' = T1_3.'department'
OUTER JOIN (
SELECT 'department', COUNT(DISTINCT('title')) AS 'scopus' FROM T1 WHERE 'scopus' LIKE "%TRUE%" GROUP BY 'department'
) AS T1_4 ON T1_1.'department' = T1_4.'department'
OUTER JOIN (
SELECT 'department', COUNT(DISTINCT('title')) AS 'Bachelor' FROM T1 WHERE 'thesis_type' LIKE "%bachelor%" GROUP BY 'department'
) AS T1_5 ON T1_1.'department' = T1_5.'department'
OUTER JOIN (
SELECT 'department', COUNT(DISTINCT('title')) AS 'Master' FROM T1 WHERE 'thesis_type' LIKE "%master%" GROUP BY 'department'
) AS T1_6 ON T1_1.'department' = T1_6.'department'