I'm getting this error when trying to run a sql select.
We had to add OPTION (HASH JOIN) to improve performance.
SELECT SecurityVpmId
FROM shared.vw_ivp_polaris_impl_periodpnl (nolock)
WHERE ( Qty != 0
OR FuncMarketValue != 0
OR DailyFuncDollarizedTradingPNL != 0
OR MTDFuncDollarizedTradingPNL != 0
OR QTDFuncDollarizedTradingPNL != 0
OR YTDFuncDollarizedTradingPNL != 0
OR LTDFuncDollarizedTradingPNL != 0)
OPTION (HASH JOIN)
Hi Marsha,
Click on the connection --> Select initial SQL
Change your query to store in a temp table
WITH CTE_INITIAL AS
(
SELECT SecurityVpmId
FROM shared.vw_ivp_polaris_impl_periodpnl (nolock)
WHERE ( Qty != 0
OR FuncMarketValue != 0
OR DailyFuncDollarizedTradingPNL != 0
OR MTDFuncDollarizedTradingPNL != 0
OR QTDFuncDollarizedTradingPNL != 0
OR YTDFuncDollarizedTradingPNL != 0
OR LTDFuncDollarizedTradingPNL != 0)
OPTION (HASH JOIN)
)
SELECT into ⌗ctedemo from CTE_INITIAL
click ok
GO to datasource pan --> Drag custom SQL --> Write Select * from ⌗ctedemo
with this way you can bring the query to tableau
Check the below link:
https://kb.tableau.com/articles/howto/using-common-table-expressions
Reason you are getting error OPTION and HAS JOIN are the native keywords for SQL server and not understandable to tableau so thats why by using initial sql you are running the query @database end and loading the data cte --> from cte to Tableau where tableau has the feasibility to read the data from view because it doesn't include the any native functions of SQL Server
Hope this helpsPlz mark this answer as correct or helpful to close the threadBR,
NB
