Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I am using the below query as part of a sql query to generate report in Direct Query mode. It throws an error while loading the dataset. It says there is a syntax error at DECLARE. But when I open the query editor it does not show any syntax error. I think in Direct Query mode it does not accept DECLARE @variablename ....... statement. Can someone suggest a workaround?
DECLARE @dates TABLE(dt datetime);
DECLARE @dateFrom datetime;
DECLARE @dateTo datetime;
SET @dateFrom = dateadd(dd,-7,getutcdate())
SET @dateTo = getutcdate()
WHILE(@dateFrom < @dateTo)
BEGIN
SELECT @dateFrom = DATEADD(minute,15,@dateFrom)
INSERT INTO @dates
SELECT @dateFrom
END;
This piece of query is at the top of the full query and then I use @dates parameter at different places in the sql query. This works fine in import mode. But I need Direct Query mode to run this.
Thanks,
Amitabh
Solved! Go to Solution.
@amitabhk1971 wrote:
Hi,
@I am using the below query as part of a sql query to generate report in Direct Query mode. It throws an error while loading the dataset. It says there is a syntax error at DECLARE. But when I open the query editor it does not show any syntax error. I think in Direct Query mode it does not accept DECLARE @variablename ....... statement. Can someone suggest a workaround?
@DECLARE @dates TABLE(dt datetime);
@DECLARE @dateFrom datetime;
@DECLARE @dateTo datetime;
@set @dateFrom = dateadd(dd,-7,getutcdate())
@set @dateTo = getutcdate()
@WHILE(@dateFrom < @dateTo)
BEGIN
@SELECT @dateFrom = DATEADD(minute,15,@dateFrom)
@INSERT INTO @dates
@SELECT @dateFrom
END;
@This piece of query is at the top of the full query and then I use @dates parameter at different places in the sql query. This works fine in import mode. But I need Direct Query mode to run this.
Thanks,
Amitabh
In DirectQuery mode, Power BI sends the SQL Statement as below, that's why you got the invalid syntax.
SELECT XXX, XXX, XXX FROM ( the sql statement input, in your case, the statement starts with DECLARE )
As a workaround, you can create a built-in calendar table with 15 minutes difference of each datetime row and query from Power BI desktop instead of generating the table on the fly.
let Source = Sql.Database("yourserver", "yourdb", [Query="select * from calendar where dt between '"&Date.ToText(Date.AddDays(Date.From(DateTimeZone.UtcNow()),-7), "yyyy/MM/dd")&"' and '"&Date.ToText(Date.From(DateTimeZone.UtcNow()), "yyyy/MM/dd")&"'"]) in Source
@amitabhk1971 wrote:
Hi,
@I am using the below query as part of a sql query to generate report in Direct Query mode. It throws an error while loading the dataset. It says there is a syntax error at DECLARE. But when I open the query editor it does not show any syntax error. I think in Direct Query mode it does not accept DECLARE @variablename ....... statement. Can someone suggest a workaround?
@DECLARE @dates TABLE(dt datetime);
@DECLARE @dateFrom datetime;
@DECLARE @dateTo datetime;
@set @dateFrom = dateadd(dd,-7,getutcdate())
@set @dateTo = getutcdate()
@WHILE(@dateFrom < @dateTo)
BEGIN
@SELECT @dateFrom = DATEADD(minute,15,@dateFrom)
@INSERT INTO @dates
@SELECT @dateFrom
END;
@This piece of query is at the top of the full query and then I use @dates parameter at different places in the sql query. This works fine in import mode. But I need Direct Query mode to run this.
Thanks,
Amitabh
In DirectQuery mode, Power BI sends the SQL Statement as below, that's why you got the invalid syntax.
SELECT XXX, XXX, XXX FROM ( the sql statement input, in your case, the statement starts with DECLARE )
As a workaround, you can create a built-in calendar table with 15 minutes difference of each datetime row and query from Power BI desktop instead of generating the table on the fly.
let Source = Sql.Database("yourserver", "yourdb", [Query="select * from calendar where dt between '"&Date.ToText(Date.AddDays(Date.From(DateTimeZone.UtcNow()),-7), "yyyy/MM/dd")&"' and '"&Date.ToText(Date.From(DateTimeZone.UtcNow()), "yyyy/MM/dd")&"'"]) in Source
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |