CODEWITHSUNDEEP
sqlsql-serverpowerbipowerquery
Helley
Helley

Reputation: 73

PowerBI / SQL : incorrect syntax

I am trying to use a SQL request in PowerBI (DirectQuery) but it is always displaying this error message : "Microsoft SQL : Incorrect syntax near the keyword 'DECLARE'. Incorrect syntax near ')'."

Here is my request (formatted to be easily readable):

= Sql.Database("BDD", "Database", [Query="
DECLARE @StartDate as Date; 
DECLARE @EndDate as Date; 
SET @EndDate = DateAdd(Day, -1, Cast(Cast(Year(Getdate()) as varchar(4)) + '/' + Cast(Month(GetDate()) as varchar(2)) + '/01' as Date)); 
SET @StartDate = DateAdd(Month, -3, DateAdd(Day, 1, @EndDate)); 
SELECT xp.[OrgaCode],
       xp.[OwnerId],
       u.[Email],
       xp.[Name],
       xc.[FileName],
       xc.[Sites],
       s.[Libellé],
       xp.[Periodicity],
       xp.[ExportId],
       xp.[Type],
       xp.[TypeExport],
       CAST(xc.[SendingSuccessDateUtc] as Date) as DateInsert 
FROM database1 xc 
INNER JOIN database2 xp ON xp.[ExportId] = xc.[ExportId] 
INNER JOIN database3 u ON u.[NumClient] = xp.[OwnerId]
INNER JOIN database4 s ON Substring([Sites], 1, 6) = s.[NumSite] 
WHERE xp.[TypeExport] = 'export-df' AND DateInsert >= @StartDate 
ORDER BY DateInsert
"])

It is working well in SQL server, so I don't understand where is the error. If someone could help me :)

Thanks!

Upvotes: 1

Views: 1033

Answers (1)

ali azizan
ali azizan

Reputation: 311

in power-bi you cannot use script in select so you must use

SELECT xp.[OrgaCode],
   xp.[OwnerId],
   u.[Email],
   xp.[Name],
   xc.[FileName],
   xc.[Sites],
   s.[Libellé],
   xp.[Periodicity],
   xp.[ExportId],
   xp.[Type],
   xp.[TypeExport],
   CAST(xc.[SendingSuccessDateUtc] as Date) as DateInsert 
FROM database1 xc 
INNER JOIN database2 xp ON xp.[ExportId] = xc.[ExportId] 
INNER JOIN database3 u ON u.[NumClient] = xp.[OwnerId]
INNER JOIN database4 s ON Substring([Sites], 1, 6) = s.[NumSite] 
WHERE xp.[TypeExport] = 'export-df' AND DateInsert >= DateAdd(Month, -3, DateAdd(Day, 1, DateAdd(Day, -1, Cast(Cast(Year(Getdate()) as varchar(4)) + '/' + Cast(Month(GetDate()) as varchar(2)) + '/01' as Date)))) 
ORDER BY DateInsert

Upvotes: 1

Related Questions