At the moment, it looks like it’s not possible to use CTE’s within an AML Model
In most cases, this does make sense, as you would create them as separate models. But we do have some users requesting being able to do this - usually because they’re taking an existing single-purpose query and want to see it in Holistics. They’re typically single purpose enough that it doesn’t warrant breaking it up into separate models.
Has anyone found any workaround to this?
Hi @david-ri,
Could you share what have you been trying to far with the CTE in query?
Sure thing.
If I create a Query Model, and use
select * from dbo.vstore
This works as expected
If I change this to
with store_list as (
select * from dbo.vstore
)
select * from store_list
This gives the error Incorrect syntax near the keyword 'with'.
If I use that same query in SQL Editor, it works fine.
I’ve also tried directly editing the AML file, from
query: @sql select * from dbo.vstore;;
to
query: @sql with store_list as (select * from dbo.vstore)
select * from store_list;;
but returns the same error as before
That is interesting as I have successfully created a SQL based model with a bunch of CTE’s included in the script, with no errors in a few occasions.
I’m not sure if it is bug then or something.
How did you create the model from the start?
Just by using ‘add query model’ in AML.
But I think you might be right about it being a bug… we predominantly use SQLS at the moment, but I do have a ‘dev’ Big Query connection, and CTE worked fine on that connection.
@Khai_To , you able to confirm this is a bug, and if so need me to raise a separate support ticket, or you able to pick up/pass on from here
We are using BigQuery without issue, so I wonder if it is a SS issue 
Hi @david-ri,
My apologies for the delay in response.
I have just checked with my team and you can refer to the image below
It’s not recommended to use CTEs with SQL Server because it only supports top-level CTE. Therefore, you should replace CTEs with SQL Models (or Views created in your Database).
Do let me know if you have any questions.
Thanks @Khai_To , the snippet you shared is from MSFT’s cte documentation. We’re very familiar with using CTE’s, and use them extensively in our work outside of Holistics.
The issue is that even when using them in this manner (which is probably 99% of use cases), they’re not being accepted by Holistics, but are when using other connection types.
Hi @david-ri,
I have just run the sample query with SQL Server 2019 and got the result below
You need to create the SQL Model for the query inside with a as () which is select 1 instead of using the CTE.
Do let me know if you have any question.
I see, so the issue is because Holistics are wrapping whatever we write into a sub query before you execute. Which of course you would have to do when referencing this model in another model.
Got it.
OK - we can work around that limitation. Would be good to get some more precise warning messages and/or documentation around the expected behaviour 
I totally agree with you David. We will be working on a documentation to explain this in more details.
Do let me know if you need further support David.
