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

[Katerina Kovriga _Stiltsoft_ · Accepted solution]

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:

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

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_ 

[Katerina Kovriga _Stiltsoft_]

That's great! Thank you for reaching out!