Is it possible to use CTE (common table expressions) when importing a data set using custom SQL? I am specifically trying to use WITH statements

[jbailey5280]

I am hitting this error "Invalid SQL: SELECT is required - No select statement found". We have enabled multi-line statements but inline queries and CTE are generally different functions. Example query attached with generalized info.

[Trifacta_Alumni]

@Joshua Bailey? From the looks of the query this seems achievable writing Trifacta transformation steps. Is there a technical reason to do this on a SQL query Vs Trifacta recipes?

[Trifacta_Alumni]

Hi @Joshua Bailey?,

Based on the error message you mention, I believe you are hitting the following limitations of multi-statement custom SQL:

• Line Termination: Each query must terminate with a semi-colon and a new line.
• SELECT requirement: In a multi-statement execution, the last statement must be a SELECT statement.

In effect, I think it is therefore not possible. I will look into this further.

Regards,

Nathanael

[Trifacta_Alumni · Accepted solution]

Hi again @Joshua Bailey?,

I have double checked, and CTEs are indeed not supported. I have filed an enhancement request on your behalf.

Best,

Nathanael

[jbailey5280]

Hi @Nathanael Kuipers? - Thanks for submitting that! I received identical info from my sales support team this morning, great that everyone is on the same page.

@Vardan Negi? - in this instance, completing the process within Trifacta transformation steps is certainly possible. In other instances, we have complex legacy queries whose CTE are difficult to parse out. It would be great to simply copy/paste them as custom SQL imports. As of now, this seems like more convenience than necessity - though it adds a few steps, I do not see any functional limitations to replicating WITH statements via recipe steps.

Thanks for your help!