Rank: Newbie
Groups: Registered
Joined: 9/10/2022(UTC)
Posts: 5
Thanks: 3 times
|
Hi all.
I have created a data source that points to a Microsoft SQLSErver Database, and created a very quick first test report using this sql code in model (i need to use the with statement):
with test as (
select *
from j
)
select * from test
when I try to check my sql code, all is correct (sql checked successfully)
but after i get the following error
ERROR[42000][Microsoft][ODBC DRIVER 17 for SQL SERVER][SQL Server]Incorrect syntax near the keyword 'with'.
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.
ERROR[42000][Microsoft][ODBC DRIVER 17 for SQL SERVER][SQL Server]Incorrect syntax near ')'.
SELECT * FROM (with test as (
select *
from j
and even when I add the semicolon at the beginning of the code I receive the same error
considering i need to use with statement, What are my options? Is there any splitter I don't know about?
|
|
|
|
|
|
Rank: Administration
Groups: Administrators
Joined: 12/20/2013(UTC) Posts: 1,209  Thanks: 14 times
Was thanked: 206 time(s) in 199 post(s)
|
Actually when using CTE (common table expression) the With keyword, you can not make a direct SQL in the model.
You should create a table in your current Data Source (Add Table) with the SQL (property "SQL Select Statement") and give an alias Name, then use it in your model.
OR
in our SQL Model, just set the property "Use Raw SQL" to true to avoid the "select * from (<your SQL>) a" behavior. It will work but you cannot set dynamic restrictions.
I hope it helps.
|
 1 user thanked epf for this useful post.
|
|
|
|
Rank: Newbie
Groups: Registered
Joined: 9/10/2022(UTC)
Posts: 5
Thanks: 3 times
|
Thanks for this useful post
|
|
|
|
|
|
Rank: Newbie
Groups: Registered
Joined: 1/25/2024(UTC) Posts: 1  Thanks: 2 times
|
Thanks for this post. I also ran into issue where one of the backend column has the reserved word Date. Trying @epf reply, looks like it can only work with SQL Model and not MetaData Model, is this correct?
Any disadvantage in using SQL Model and not MetaData Model?
|
|
|
|
|
|
Rank: Administration
Groups: Administrators
Joined: 12/20/2013(UTC) Posts: 1,209 Thanks: 14 times
Was thanked: 206 time(s) in 199 post(s)
|
Yes, you should always create a Data Source on your database when possible, so all reports can reference its table definitions and joins, etc.
Thus when you start to have several reports, the maintenance is easy (e.g. you can change the format of an element in the Data Source, then all the reports will get the new format).
Check Northwind demo: One Data Source with many reports...
|
|
|
|
|
|
Forum Jump
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.
