时间戳

首页 > 解决方案 > PowerBI / SQL：语法不正确

sql - PowerBI / SQL：语法不正确

问题描述

我正在尝试在 PowerBI (DirectQuery) 中使用 SQL 请求，但它始终显示此错误消息：“Microsoft SQL：关键字'DECLARE'附近的语法不正确。')'附近的语法不正确。”

这是我的请求（格式化为易于阅读）：

= 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
"])

它在 SQL Server 中运行良好，所以我不明白错误在哪里。如果有人可以帮助我:)

谢谢！

标签： sqlsql-serverpowerbipowerquery

解决方案

在 power-bi 中，您不能在 select 中使用脚本，因此您必须使用

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

推荐阅读