Can CTE be used inside a FROM subquery?

[Deepak Puri]

This issue came up in the MSDN Analysis Services Forum, so I was
wondering if there's any workaround:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=380689&SiteID=1

When a Named Query is used in lieu of a table in an SSAS Data Source
View (DSV), the generated SQL query apparently embeds the original query
as a subquery in the FROM clause. So, if a DSV Named Query includes a
CTE, the WITH CTE clause also goes inside the subquery, which doesn't
seem to work. The example from the above thread is:

Original DSV Named Query:
>>
WITH mycte AS (SELECT TOP (200) object_id, name, column_id,
system_type_id
FROM sys.columns)
SELECT object_id, name, column_id, system_type_id
FROM mycte as mycte_1
>>

Generated T-SQL Query, which has a parsing error:
>>
select [CTETest].*
from
(
WITH mycte AS (SELECT TOP (200) object_id, name, column_id,
system_type_id
FROM sys.columns)
SELECT object_id, name, column_id, system_type_id
FROM mycte as mycte_1
) AS [CTETest]
>>

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***

[Erland Sommarskog]

Deepak Puri (deepa...@progressive.com) writes:
> Generated T-SQL Query, which has a parsing error:
>>>
> select [CTETest].*
> from
> (
> WITH mycte AS (SELECT TOP (200) object_id, name, column_id,
> system_type_id
> FROM sys.columns)
> SELECT object_id, name, column_id, system_type_id
> FROM mycte as mycte_1
> ) AS [CTETest]
>>>

This query works:

WITH mycte AS (SELECT TOP (200) object_id, name, column_id,
system_type_id
FROM sys.columns)

select [CTETest].*
from
(

SELECT object_id, name, column_id, system_type_id
FROM mycte as mycte_1
) AS [CTETest]

That is, the CTE should be at the head of the outer query.

If Analysis Services generates the incorrect syntax, I assume that
this is a bug in AS, and I suggest that you submit a bug on
http://lab.msdn.microsoft.com/ProductFeedback/.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[ML]

How about a temporary table?

declare @tempCte table([name] sysname)

begin
with s
as (
select [name]
from sys.objects
)
insert @tempCte
(
[name]
)
select [name]
from s
end

select [name]
from @tempCte

ML

---
http://milambda.blogspot.com/

[Jamie T]

ML,
That won't work unfortunately because you cannot put procedural code
into a DSV named query. That makes sense to be honest because a DSV
named query is analogous to a View rather than a sproc.

Erland,
It was I that originally raised this (thanks to Deepak for running with
it) so thank you for your feedback.

-Jamie