Can CTE be used inside a FROM subquery?

This issue came up in the MSDN Analysis Services Forum, so I was
wondering if there's any workaround:
http://forums.microsoft.com/MSDN/Sh...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.examnotes.net ***Deepak Puri (deepak_puri@.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, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||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/|||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
ML wrote:
> 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/