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
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
Solved! Go to Solution.
- Labels:
- Trifacta Classic
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!
