CTE in a DSV named query

Hello,

I have a CTE that I want to put into a DSV named query. here is the CTE (which anyone can run):

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

I can put that into the named query editor and run it, no problems. Upon clicking "OK" I get:

Incorrect syntax near ')'

Incorrect syntax near the keyword 'with'/ If this statement is a common table expression or an xmlnamespaces clause the previous statement must be terminated with a semicolon.

So, I put a semicolon in front of it so it looks liek this:

;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

This time I get:

Unable to parse query text

Incorrect syntax near ')'

Incorrect syntax near ';'

Please don't tell me that CTEs are not allowed in a DSV. That would be very VERY bad.

Please will someone try this for me? Can you repro the problem?

Thanks

Jamie

Jamie,

You might want to pose this question to the SQL Server Programming folks as well - from what I can tell, the problem is that the Named Query is embedded as a subquery, and I'm not sure how/whether a CTE can be used in a subquery. The generated query looks like:

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]

Defining the CTE at the outer select works, but obviously doesn't help for the DSV:

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]

|||

Thanks Deepak,

Fundamentally though, the fact that you can't put valid T-SQL into a DSV is bad bad bad wouldn't you agree?

I'm not too enamoured with this.

-Jamie

|||Jamie, I share your frustation - just curious whether the CTE scenario was considered when the DSV/Named Query framework was being architected? Maybe someone from MS can shed some light on this, meanwhile I'll post a question to see if there's any work-around on the relational side...|||

To follow up - I got this response from Erland on the SQL Server Programming newsgroup, which suggests that an SSAS bug report be opened. There are some XSL Cartridge files that can be tweaked to change the generated SQL for other databases (DB2, SQL 2000, etc), but I'm not sure that there is a cartridge for SQL Server 2005 itself.

http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/5150eba86d06e5b0

>>

microsoft.public.sqlserver.programming > Can CTE be used inside a FROM subquery?

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
...

>>

|||

Thank you Deepak (and to Erland). I have raised this at the feedback centre - feel free to vote for it and add comments.

http://lab.msdn.microsoft.com/ProductFeedback/viewFeedback.aspx?feedbackId=FDBK49479

Thanks

Jamie

|||

The reason why we are trying to wrap it as sub select statement is to avoid some statements like Create, Delete, order by.

You can try to remove the subselect capability in the cartridge by removing the line in Sql2000.xsl

<mssqlcrt:supports-subselect />

The cartridges are located in the following directories for tools and engine respectively.

C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\DataWarehouseDesigner\UIRdmsCartridge

C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\bin\Cartridges

After removing this, you should be able to create the named query but it may hurt the performance when processing in engine.