Issues merging tables that are gathered using Table Excerpt Include macros.

Domantas Povilaitis

Hello,

At the moment we have 10 different tables, where manual input is necessary from different teams in our organization. For the sake of clarity, we want to create one master table, which would gather all of the necessary information in one place for us to have a high-level overview of statuses. 

All 10 tables were gathered into one Confluence page using the "Table Excerpt Include" macro. At the moment we are stuck on merging all these tables into one coherent table using SQL. When writing the code it seems that the Confluence does not detect any errors in the code itself, however, when the page is saved an error pops up with a message, that a particular column does not exist (we are 100% sure that it does). 

Below is the SQL code we are using to merge the tables.

Any help is appreciated! 

---

SELECT DISTINCT T1.'Page'AS'Page',T1.'Configuration Item'AS'Configuration Item',T1.'Deviation Reason'AS'Deviation Reason',T1.'To Be Standardized'AS'To Be Standardized',T1.'Standardization Approach'AS'Standardization Approach'
FROM T1
UNION
SELECT DISTINCT T2.'Page'AS'Page',T2.'Models Configuration Name'AS'Configuration Item',T2.'Deviation Reason'AS'Deviation Reason',T2.'To Be Standardized'AS'To Be Standardized',T2.'Standardization Approach'AS'Standardization Approach'
FROM T2
UNION
SELECT DISTINCT T3.'Page'AS'Page',T3.'Type'AS'Configuration Item',T3.'Deviation Reason'AS'Deviation Reason',T3.'To Be Standardized'AS'To Be Standardized',T3.'Standardization Approach'AS'Standardization Approach'
FROM T3
UNION
SELECT DISTINCT T4.'Page'AS'Page',T4.'Name'AS'Configuration Item',T4.'Deviation Reason'AS'Deviation Reason',T4.'To Be Standardized'AS'To Be Standardized',T4.'Standardization Approach'AS'Standardization Approach'
FROM T4
UNION
SELECT DISTINCT T5.'Page'AS'Page',T5.'Portfolio Matrix Name'AS'Configuration Item',T5.'Deviation Reason'AS'Deviation Reason',T5.'To Be Standardized'AS'To Be Standardized',T5.'Standardization Approach'AS'Standardization Approach'
FROM T5
UNION
SELECT DISTINCT T6.'Page'AS'Page',T6.'Configuration Item'AS'Configuration Item',T6.'Deviation Reason'AS'Deviation Reason',T6.'To Be Standardized'AS'To Be Standardized',T6.'Standardization Approach'AS'Standardization Approach'
FROM T6
UNION
SELECT DISTINCT T7.'Page'AS'Page',T7.'Portfolio Name'AS'Configuration Item',T6.'Deviation Reason'AS'Deviation Reason',T7.'To Be Standardized'AS'To Be Standardized',T7.'Standardization Approach'AS'Standardization Approach'
FROM T7
UNION
SELECT DISTINCT T8.'Page'AS'Page',T8.'Reduction Rule Name'AS'Configuration Item',T8.'Deviation Reason'AS'Deviation Reason',T8.'To Be Standardized'AS'To Be Standardized',T8.'Standardization Approach'AS'Standardization Approach'
FROM T8
UNION
SELECT DISTINCT T9.'Page'AS'Page',T9.'Service Code'AS'Configuration Item',T9.'Deviation Reason'AS'Deviation Reason',T9.'To Be Standardized'AS'To Be Standardized',T9.'Standardization Approach'AS'Standardization Approach'
FROM T9
UNION
SELECT DISTINCT T10.'Page'AS'Page',T10.'Configuration Item'AS'Configuration Item',T10.'Deviation Reason'AS'Deviation Reason',T10.'To Be Standardized'AS'To Be Standardized',T10.'Standardization Approach'AS'Standardization Approach'
FROM T10

Katerina Rudkovskaya _Stiltsoft_

Hi @Domantas Povilaitis,

The structure of your query seems to be correct. So, you may check the source tables’ headers for typos and type in the query using the Table Transformer macro’s hints (type in, for example, “T1.” and choose the required column from the menu).

I can also suggest another query for your case:

SELECT
DISTINCT COALESCE('New 1', 'Text 1') AS 'Page 1',
COALESCE('New 2', 'Text 2') AS 'Page 2'
FROM T*

Here I merge two tables: with headers “New 1”, “New 2” and “Text 1”, “Text 2”.


The COALESCE function is used to list all the possible variations of the column names that you want to rename in a specific way.



So, your original SQL query will be much shorter and easier to read.

And you may also use the following structure: rename your columns on the page with the Table Excerpt macro: table <- Table Transformer <- Table Excerpt (inside the Table Toolbox macro, of course). Then you’ll be able to combine all the tables into one big report by checking the corresponding option inside the Table Excerpt Include macro.

And the simplest way is to collect tables with the same headers (like you do with the Page Properties/Page Properties Report macros). Then you’ll be able to exclude the Table Transformer macro from the case.