Incorrect syntax near ';' - Works in SQL Server, not from Servicestack.ORMLite

Question

I am executing a SQL statement through Servicestack.ORMLite. The statement is of the following format:

with rowdata as (
select t1.v1, t1.v2 datakey, t2.v1 datavalue from t1
left join t2 on t2.rowid = t1.rowid      
)
select * from rowdata
PIVOT
(
    SUM(datavalue)
    FOR datakey IN ([1],[2],[3],[4],[5])
)AS PivtTable

This executes correctly in SQL Server, and in Oracle (with a few small changes). However, when executing through Servicestack.ORMLite using the 'db.Select (sql)' command, I get the following error:

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

Terminating with a semicolon just returns the following error:

Incorrect syntax near ';'

Executing every other 'Select' statement works fine, but not if it begins with a 'with' or anything else it seems. But this does not appear to be a Servicestack.ORMLite error - both are System.Data.SqlClient.SqlExceptions. If I copy the generated code generated in debug into SQL Server Management studio, it works fine, but execute the same code though ORMLite and it fails.

Any ideas?

Answer 1

Servicestack.ORMLite does not like CTEs. You should look at using the Custom SQL APIs, see here

Something like this:

var sql = @"with rowdata as (
select t1.v1, t1.v2 datakey, t2.v1 datavalue from t1
left join t2 on t2.rowid = t1.rowid      
)
select * from rowdata
PIVOT
(
    SUM(datavalue)
    FOR datakey IN ([1],[2],[3],[4],[5])
)AS PivtTable"

List<MyType> results = db.SqlList<MyType>(sql);