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 

[Stiltsoft support · Accepted solution]

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. 

[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.

[MudCo]

Also, just wanted to let you know, I am using an Enterprise edition and it has confidential data. I will not be able to provide the link or the data even in the support page you linked. 

[Stiltsoft support]

Then you may create a new test page with a mini table with dummy data but preserve original headings.

Then copy and paste your macro with the current query on this new page.

[Stiltsoft support]

And the T1 in the external SELECT caught my eye - you haven't removed it.

[MudCo]

Yes, you are right, I missed that for external it is not required. 

Now it shows all the values.

[MudCo]

It finally works exactly as I expected. Thank you so much for your assistance