cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Temp Tables in Power BI with Direct Query

I am trying to run a SQL query in Power BI using the direct query option. It loads fine into the Query Editor but when I click "close and apply", I'm getting an error saying the following: Incorrect syntax near the keyword 'IF'. Incorrect syntax near ')'

 

Also, this works fine in SSMS. Any ideas?

 


--==== Users Table ====--
IF OBJECT_ID('tempdb.dbo.#SCUsers') is not null DROP TABLE #SCUsers
BEGIN
Select
u.usr_key
,u.usr_arc_id [ARCInitials]
,u.usr_first_name+' '+u.usr_last_name [UserName]
INTO #SCUsers
FROM [SelectCARE-SQL].dbo.users u WITH(NOLOCK)
create clustered index cix_#SCUsers on #SCUsers(usr_key)
END

IF OBJECT_ID('tempdb.dbo.#ArcCase') is not null DROP TABLE #ArcCase
BEGIN
Select
ac.arc_ref [ReferenceNumb]
,ac.act_key [AccountID]
INTO #ArcCase
FROM [SelectCARE-SQL].dbo.arc_cases ac
WHERE ac.arc_add_date >= CAST(GETDATE()-7 AS DATE)
create clustered index cix_#ArcCase on #ArcCase(AccountID)
END

--==== Account Create Dates ====--
IF OBJECT_ID('tempdb.dbo.#SCAccts') is not null BEGIN DROP TABLE #SCAccts END
BEGIN
Select
ac.ReferenceNumb
,a.act_key [AccountID]
,a.act_add_date [AcctAddDate]
,a.act_parent_key [ParentAccountID]
,ls.sta_title [LeadStatus]
,l.lea_tier [LeadTier]
,agent.UserName [Agent]
,agent.ARCInitials
,origu.UserName [OrigAgent]
,origu.ARCInitials [OrigInitials]
,case when a.act_parent_key is null then 1 else 0 end [OpCount]
,case when l.lea_status in (
/* Test Statuses */ '291','364','371','310','385'
/* Dupe Statuses */ ,'94','187','186','184'
/* Invalid Status */ ,'16') THEN 1 ELSE 0 END [ExcludeAccts]
,row_number() OVER (PARTITION BY a.act_key ORDER BY a.act_web_taken_datetime asc ) [RN]
INTO #SCAccts
FROM [SelectCARE-SQL].dbo.Accounts a
LEFT JOIN #ArcCase ac on ac.AccountID = a.act_key
LEFT JOIN [SelectCARE-SQL].dbo.Individuals i WITH(NOLOCK) ON i.indv_account_id = a.act_key
LEFT JOIN [SelectCARE-SQL].dbo.leads l WITH(NOLOCK) on l.lea_account_id = a.act_key
LEFT JOIN [SelectCARE-SQL].dbo.statuses ls WITH(NOLOCK) ON l.lea_status = ls.sta_key
LEFT JOIN #SCUsers agent ON agent.usr_key = a.act_assigned_usr
LEFT JOIN #SCUsers origu ON origu.usr_key = act_original_usr
WHERE 1=1
and a.act_add_date >= CAST(GETDATE()-7 AS DATE)
END


IF OBJECT_ID('tempdb.dbo.#QuoteDate') is not null BEGIN DROP TABLE #QuoteDate END
BEGIN
Select
qa.quoteid
,min(qa.date_stamp) [MinDate]
INTO #QuoteDate
FROM squdb.dbo.QuoteAudit qa WITH(NOLOCK)
WHERE 1=1
AND (qa.status in ('QUO','PKG','NEW') OR qa.status is null)
AND qa.date_stamp >= getdate()-3
GROUP BY qa.quoteid
HAVING min(qa.date_stamp) >= CAST(GETDATE()-7 AS DATE)
create clustered index cix_#QuoteDate on #QuoteDate(quoteid)
END

IF OBJECT_ID('tempdb.dbo.#BaseCaseData') is not null BEGIN DROP TABLE #BaseCaseData END
BEGIN
Select
po.ReferenceNumb
,sca.AccountID
,sca.ParentAccountID
,po.QuoteID
,po.PolicyID
,po.PlanID
,po.PolicyNumber
,po.CompanyCode
,po.Status
,case when po.ApplicSubType = 'AU' THEN 'AU' ELSE 'Non-AU' END [AU Flag]
,po.PremiumAnnually
,cast(po.CoverInitiatedDate as date) [COV_Date]
,convert(varchar(7),po.CoverInitiatedDate,126) [COV_Month]
,cast(po.CoverRequestDate as date) [CRI_Date]
,convert(varchar(7),po.CoverRequestDate,126) [CRI_Month]
,sca.Agent
,sca.ARCInitials
,sca.OrigAgent
,sca.OrigInitials
,coalesce(sca.AcctAddDate,qd.MinDate) [CreateDate]
,convert(varchar(7),coalesce(sca.AcctAddDate,qd.MinDate),126) [CreateMonth]
,sca.LeadTier
,isnull(sca.OpCount,0) [OpCount]
,1 [CaseCount]

INTO #BaseCaseData
FROM #SCAccts sca
LEFT JOIN squdb.dbo.Policy po WITH(NOLOCK) ON po.ReferenceNumb = sca.ReferenceNumb
LEFT JOIN #QuoteDate qd ON qd.quoteid = po.QuoteID
WHERE 1=1
AND sca.RN = 1
END


IF OBJECT_ID('tempdb.dbo.#AgentLevels') is not null drop table #AgentLevels
BEGIN
Select
*
INTO #AgentLevels
FROM BizInt_SQL.dbo.AgentHistory_Apr2020 WITH(NOLOCK)
END

IF OBJECT_ID('tempdb.dbo.#OpsData') is not null DROP TABLE #OpsData
BEGIN
Select
po.ReferenceNumb
,po.AccountID
,po.Status
,po.OPCount
,1 [CaseCount]
,CAST(po.CreateDate as date) OPDate
,po.CreateMonth
,CAST(po.COV_Date as date) COV_Date
,po.COV_Month
,CAST(po.CRI_Date as date) CRI_Date
,po.CRI_Month
,po.[AU Flag]
,po.PremiumAnnually
,po.ARCInitials [AgentInitials]
,po.Agent [Agent]
,al.ManagerName [Manager]
,al.AgentLevelLabel
,al.AgentLocation
,CONCAT(po.Agent,' ',CAST(po.CreateDate as date)) Combine
,case when po.CRI_Date is NULL then NULL
when cast(po.CreateDate as date) = po.CRI_Date THEN 'Same Day'
WHEN po.CRI_Date <> cast(po.CreateDate as date) THEN 'Pipeline'
ELSE NULL END [SameDay Flag]

INTO #OpsData
FROM #BaseCaseData po
LEFT JOIN #AgentLevels al on al.AgentARCInitials = po.ARCInitials and cast(po.CreateDate as date) between al.StartDate and al.EndDate
WHERE 1=1
END

Select
*
FROM #OpsData od
WHERE 1=1
AND Agent IS NOT NULL
AND Manager IS NOT NULL
AND AgentLevelLabel<>'CNT'
AND AgentLevelLabel<>'LHA'
ORDER BY od.OPDate desc

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Temp Tables in Power BI with Direct Query

Hi @michaelamyer ,

Power BI Desktop has issues on parsing Queries in Direct Query mode. It always resolves the front end input SQL statement as a derived table so that stored procedure will cause syntax error.

For now, there are two ways to resolve it. One is changing the connection mode from Direct Query to Import. And the other is building most of data sets in SQL server using views etc and then direct connect to those.

 

Please reference the similar threads:

            (It introduced the two ways in detail.)

            (The user resolved it using a new function. But in this way, it may change to import mode. )

 

You could vote the idea up to get the requirement of using stored procedure with DQ mode implemented quickly.

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/33504791-direct-query

 

Hope this can help you.😊

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
Highlighted
Community Support
Community Support

Re: Temp Tables in Power BI with Direct Query

Hi @michaelamyer ,

Power BI Desktop has issues on parsing Queries in Direct Query mode. It always resolves the front end input SQL statement as a derived table so that stored procedure will cause syntax error.

For now, there are two ways to resolve it. One is changing the connection mode from Direct Query to Import. And the other is building most of data sets in SQL server using views etc and then direct connect to those.

 

Please reference the similar threads:

            (It introduced the two ways in detail.)

            (The user resolved it using a new function. But in this way, it may change to import mode. )

 

You could vote the idea up to get the requirement of using stored procedure with DQ mode implemented quickly.

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/33504791-direct-query

 

Hope this can help you.😊

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Kudoed Authors