cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

SQL parameters in direct query

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

1 ACCEPTED SOLUTION
Microsoft
Microsoft


@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


@amitabhk1971

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

 

 

View solution in original post

1 REPLY 1
Microsoft
Microsoft


@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


@amitabhk1971

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

 

 

View solution in original post

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors