logo

Warning: The forum is now for consultation only. Please use GitHub Discussions to post any questions or comments.


Welcome Guest ! To enable all features please Login or Register.

Notification

Icon
Error

Options
Go to last post Go to first unread
Androoda50  
#1 Posted : Friday, December 9, 2022 10:13:09 AM(UTC)
Androoda50

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?
epf  
#2 Posted : Saturday, December 10, 2022 9:16:23 AM(UTC)
epf

Rank: Administration

Groups: Administrators
Joined: 12/20/2013(UTC)
Posts: 1,209
Switzerland

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.
thanks 1 user thanked epf for this useful post.
Androoda50 on 12/12/2022(UTC)
Androoda50  
#3 Posted : Monday, December 12, 2022 9:25:27 AM(UTC)
Androoda50

Rank: Newbie

Groups: Registered
Joined: 9/10/2022(UTC)
Posts: 5

Thanks: 3 times
Thanks for this useful post
rede  
#4 Posted : Friday, January 26, 2024 3:33:51 PM(UTC)
rede

Rank: Newbie

Groups: Registered
Joined: 1/25/2024(UTC)
Posts: 1
United States

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?
epf  
#5 Posted : Saturday, January 27, 2024 8:58:37 AM(UTC)
epf

Rank: Administration

Groups: Administrators
Joined: 12/20/2013(UTC)
Posts: 1,209
Switzerland

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...
Users browsing this topic
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.