Can you use CTEs (WITH syntax) in a PDT? (common table expressions, aka WITH param)

[Morgan1]

Knowledge Drop

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.