Subquery SQL during dataprep


Is it possible to write this subquery using SQL in data prep?

SELECT col1, col2, col3,
FROM subquery AS subquery 2
WHERE subquery2.col2 <= subquery.col2) AS “average col3”)
//subquery codes here//) AS subquery

I have no problem with writing this code without the subquery part starting with “SELECT AVG(col3)…”. But when I write the full code above there is an error

sourceErrorCode: 0
sourceErrorMessage:ERROR: relation “subquery” does not exist
sourceErrorState: 42P01

Hi @Bunnydata - What is your source database? I personally prefer CTE ( Common Table Expression) over subquery. Can you give the exact requirement so that we can help you. Subquery should work but from performance stand point try to explore CTE.

Regards - Sanjeeb

1 Like

Hi @Sanjeeb2022 , my data is stored in RedShift.

I did post the exact requirement here trying to use calculated field but unable, so I try to use SQL. The solution provided in this question does not work.

hi @Bunnydata - can you please share your whole query to check this? also can you use CTE instead of sub query?

Regards - Sanjeeb

thank you @Sanjeeb2022 . I was able to run this using CTE!!!


Thank you @Bunnydata . Can you please marked the suggestion as solution so that it can help to other community member.

Regards - Sanjeeb

1 Like