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; } } }

Table transformer - Multiple sql queries

Karthika_Natarajan

I have a result table for which I used 3 table transformers. Because I wanted to use the result table of one query as a source table for the other since I add new columns to each query. Which is the best way to do it? I tried nested queries but it doesn't work.

My queries are like this

--Select rownum() as 'RN' , T1.'First column' from T1 -----> RN is the new column added

--Select

case when rownum() in (2) then "Test" else null end as 'Status', T1.First column from T1 ---- Status is the new column

Accepted answers

Katerina Kovriga {Stiltsoft}

Hi @Karthika_Natarajan ,

You may try to nest the second query in the FROM destination of the first query. Here is an example for you:

Wed 1-1.png

Wed 1-2.png

SELECT *,
CASE WHEN 'RN' = 2 THEN "Test"
ELSE NULL
END
AS 'Status'
FROM (SELECT rownum() as 'RN', T1.'Project' FROM T1)

Wed 1-3.png

Hope this may help your case.

All comments

Katerina Kovriga {Stiltsoft}

Hi @Karthika_Natarajan ,

You may try to nest the second query in the FROM destination of the first query. Here is an example for you:

Wed 1-1.png

Wed 1-2.png

SELECT *,
CASE WHEN 'RN' = 2 THEN "Test"
ELSE NULL
END
AS 'Status'
FROM (SELECT rownum() as 'RN', T1.'Project' FROM T1)

Wed 1-3.png

Hope this may help your case.

Karthika_Natarajan

Thank you @Katerina Kovriga _Stiltsoft_ , But I create few new columns in my query I get this error 'TypeError: Cannot read properties of undefined (reading 'RN')'

SELECT
CASE
WHEN T1.'RN' IN (1,5,9)
THEN "Test"
WHEN T1.'RN' IN (2,6,10)
THEN "Dev"
WHEN T1.'RN' IN (3,7,11)
THEN "Int"
WHEN T1.'RN' IN (4,8,12)
THEN FORMATWIKI("{cell:bgColor=#E3FCEF}Total {cell}")
END AS 'Status',
CASE
WHEN T1.'RN' IN (1,2,3,4)
THEN T1.'Project' END AS 'Car',
CASE
WHEN T1.'RN' IN (5,6,7,8)
THEN T1.'Project' END AS 'Auto',
CASE
WHEN T1.'RN' IN (9,10,11,12)
THEN T1.'Project' END AS 'Bus'
FROM (SELECT ROWNUM() AS 'RN',
T1.'Project' FROM T1 WHERE T1.'Project' NOT IN ('Project'))

Katerina Kovriga {Stiltsoft}

Try to remove the T1."XXX" reference from the first part of your query:

SELECT
CASE
WHEN T1.'RN' IN (1,5,9)
THEN "Test"
WHEN T1.'RN' IN (2,6,10)
THEN "Dev"...

---->

SELECT
CASE
WHEN 'RN' IN (1,5,9)
THEN "Test"
WHEN 'RN' IN (2,6,10)
THEN "Dev"...

The query tries to look for the "RN" column in the first table but there is no this column at this stage, we add it in the end of the query.

My original example looks like that:

SELECT *,
CASE WHEN 'RN' = 2 THEN "Test"
ELSE NULL
END
AS 'Status'
FROM (SELECT rownum() as 'RN', T1.'Project' FROM T1)

Karthika_Natarajan

It worked Thanks a lot @Katerina Kovriga _Stiltsoft_

Welcome!

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

Quick Links

Find more posts tagged with

confluence

server

confluence-server