View the original community article here
Last tested: Jul 1, 2020
You can use CTEs in PDTs, and you can use them in DTs. However, it’s not recommended to use CTEs in PDTs or DTs. Using a CTE can work…but it can also break, and we don’t know when it will nor do we test for it.
Ephemeral Derived Tables are created using a WITH statement. Therefore, using a CTE in a DT would create nested WITH statements, which is not allowed in SQL. You may see an error like Syntax error at or near WITH
Recommended Fix: take the sql from your CTE and use that in a derived table, then reference that back in the PDT/DT that you are pulling the CTE out of
view: cte_replacement {
derived_table: {
sql: SELECT id FROM analyst ;;
}
}
view: users_b {
derived_table: {
sql: SELECT * FROM ${cte_replacement.SQL_TABLE_NAME} ;;
}
}
This content is subject to limited support.