Hi,
I have a query with multiple CTEs and it works in Athena. But when I use the same query in quicksight dataset custom SQL, the new column “extracted_value3” from the final CTE "Finalcte4 " is not visible.
WITH cte1 AS (
SELECT
column1,
split(column2,',') as array_column
FROM
my_table
),
cte2 AS (
SELECT
column1,
REGEXP_SUBSTR(value, 'pattern1') AS extracted_value1
FROM
cte1
GROUP BY
column1
),
cte3 AS (
SELECT
column1,
split(extracted_value1, ':') AS extracted_value2
FROM
cte2
),
Finalcte4 AS (
SELECT
column1,
split(extracted_value2, '\') AS extracted_value3
FROM
cte3
),
SELECT
column1,
extracted_value3
FROM
Finalcte4
The output in custom SQL only has column1 but no extracted_value3.
In Athena,
| Column1 | extracted_value3 |
|---|---|
| value1 | extracted_val1 |
| value2 | extracted_val2 |
In Custom SQL,
| Column1 |
|---|
| value1 |
| value2 |
I wonder if the Athena query and Custom SQL query is different? Can anyone also help me to make the Finalcte4 extracted_value3 column visible in the custom SQL? Thank you!
