nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Subquery (nested select) in Table transformer

MudCo

Hi There,

I have a table which is wrapped in multiple table transformers as I could not get them in one. Could be a limitation in my sql coding skills or maybe the macros work this way...but would would to get help on this. I want to reduce the number of table transformers I have as this page has reports as well which also includes more such macros.

The order from innermost (1) to outer transformer (4):

Table Transformer 1- This is ensuring the value entered casts in the right format such as a comma, after publishing. As in 100,000 instead of 100000

SELECT *, CAST(T1.'Contract Value In AUD' AS numwithcomma) As '*Contract Value In AUD' FROM T*

Table Transformer 2- This assigns a 'P' rating to the different range of values in column T1.Contract Value in AUD' and ends as a new column called 'Priority Rating'

SELECT *, CASE WHEN ('*Contract Value In AUD') <= 500000 THEN "P4"

WHEN ('*Contract Value In AUD') BETWEEN 500001 AND 5000000 THEN "P3"

WHEN ('*Contract Value In AUD') BETWEEN 5000001 AND 20000000 THEN "P2"

WHEN ('*Contract Value In AUD') >=20000000 THEN "P1"

END AS '*Priority Rating' FROM T*

Table Transformer 3- This gives a new column called 'Work Start Date' as per an interval condition given to each 'P' rating. The reference for calculation is column 'T1.Date Contract Will Expire'. Eg., if the value of a row in this column is '30 Sep 2024' and if it's a P4, then as per the interval condition given, the value published is '01 Aug 2024' (2 months as it satisfies the first condition)

Select *,

CASE WHEN '*Priority Rating' = "P4" THEN FORMATDATE(DATE_SUB(T1.'Date Contract will Expire',INTERVAL 2 MONTH))

WHEN '*Priority Rating' = "P3" THEN FORMATDATE(DATE_SUB(T1.'Date Contract will Expire',INTERVAL 3 MONTH))

WHEN '*Priority Rating' = "P2" THEN FORMATDATE(DATE_SUB(T1.'Date Contract will Expire',INTERVAL 5 MONTH))

WHEN '*Priority Rating' = "P1" THEN FORMATDATE(DATE_SUB(T1.'Date Contract will Expire',INTERVAL 6 MONTH))

END AS '*Work Start Date' FROM T*

Transformer 4- This one is just selecting the columns I want from the table and in the order I need

Accepted answers

Stiltsoft support

Hi @MudCo ,

Seems that you use our Table Transformer macro that is provided by the app that we develop - Table Filter, Charts & Spreadsheets for Confluence.

You may check how to write nested SELECTs in this Community thread: https://community.atlassian.com/t5/Confluence-questions/Insert-numbering-Column-in-Table-Filter-and-Charts-for/qaq-p/1810565

The only trick here is that in the internal SELECT you may (or may not) refer to your columns as T1.'Column name' and to your table as FROM T1.

For all the external SELECTS you use just 'Column name' (after the internal SELECT we can't refer to T1, we work with an already modified table).

SELECT ('Number' + 2) AS 'Number',

'Key', 'Summary' - simple column names for the external SELECT

FROM (

SELECT ROWNUM() as 'Number',

T1.'Key', T1.'Summary' - T1.'Column name' only for the first internal SELECT

FROM T1)

So, you may combine your first three queries into one.

What concerns the fourth Transformer, it seems to be an extra one even without nested SELECTS - just list the required columns in the third query. Don't use * but list what you need.

If you are stuck with something, you may refer to our support portal that is confidential. Attach your page storage (upper right corner of the page -> menu ... -> View storage format), we'll recreate your macros and queries and try to help.

All comments

Stiltsoft support

Hi @MudCo ,

Seems that you use our Table Transformer macro that is provided by the app that we develop - Table Filter, Charts & Spreadsheets for Confluence.

You may check how to write nested SELECTs in this Community thread: https://community.atlassian.com/t5/Confluence-questions/Insert-numbering-Column-in-Table-Filter-and-Charts-for/qaq-p/1810565

The only trick here is that in the internal SELECT you may (or may not) refer to your columns as T1.'Column name' and to your table as FROM T1.

For all the external SELECTS you use just 'Column name' (after the internal SELECT we can't refer to T1, we work with an already modified table).

SELECT ('Number' + 2) AS 'Number',

'Key', 'Summary' - simple column names for the external SELECT

FROM (

SELECT ROWNUM() as 'Number',

T1.'Key', T1.'Summary' - T1.'Column name' only for the first internal SELECT

FROM T1)

So, you may combine your first three queries into one.

What concerns the fourth Transformer, it seems to be an extra one even without nested SELECTS - just list the required columns in the third query. Don't use * but list what you need.

MudCo

Ah I see

I followed the method in the other page you linked and tried to combined 3. Realised, the order was the opposite of what I had give, so the first table transformer code will become the innermost select statement.

Here is my combined code. With this, my new column '*Work Start Date' returned empty cells. My SQL skills are quite basic and I am unable to find what's wrong with my code.

Select *,

CASE

WHEN 'T1.*Priority Rating' = "P4" THEN FORMATDATE(DATE_SUB(T1.'Date Contract will Expire',INTERVAL 2 MONTH))

WHEN 'T1.*Priority Rating' = "P3" THEN FORMATDATE(DATE_SUB(T1.'Date Contract will Expire',INTERVAL 3 MONTH))

WHEN 'T1.*Priority Rating' = "P2" THEN FORMATDATE(DATE_SUB(T1.'Date Contract will Expire',INTERVAL 5 MONTH))

WHEN 'T1.*Priority Rating' = "P1" THEN FORMATDATE(DATE_SUB(T1.'Date Contract will Expire',INTERVAL 6 MONTH))

END AS '*Work Start Date'

FROM

( SELECT *,

CASE WHEN ('*Contract Value In AUD') <= 500000 THEN "P4"

WHEN ('*Contract Value In AUD') BETWEEN 500001 AND 5000000 THEN "P3"

WHEN ('*Contract Value In AUD') BETWEEN 5000001 AND 20000000 THEN "P2"

WHEN ('*Contract Value In AUD') >=20000000 THEN "P1"

END AS '*Priority Rating'

FROM

(SELECT *, CAST('Contract Value In AUD' AS numwithcomma) As '*Contract Value In AUD' FROM T1))

Stiltsoft support

As I see it, you are combining it wrong. In the internal SELECT you work with the '*Contract Value In AUD' column and set statuses by it. But you create this column in the external SELECT.

So, again you may refer to our support to see the whole picture at once.

MudCo

Apologies, I was editing my response. Please have a look at my edited response. I realised my error immediately.

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

confluence

sql

filter

data-center

table

table-filter

table-filter-and-charts

confluence-data-center

table-transformer

table-filter-charts-spreadsheets