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

Accepted Solutions
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
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors