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
