I have the following query:
select afp.period_start , afp.period_end , COUNT(*) from services s Left join ( select entityId, resolutionDate from ... ) SI on si.entityId = s.Id left join #aff_periods afp -- or left hash join #aff_periods afp on isnull(si.reslutionDate, GETDATE()) <= afp.period_end group by afp.period_start , afp.period_end option (hash join); -- or this
Whenever adding either the query-level hint or join-level hint I get the following error.
Msg 8622, Level 16, State 1, Line 50
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.
I've read some posts online suggesting that this might be because of a index on the table, so I dropped the non-clustered index I had created on that table and tried to re-run the query but I still get the error.
Any suggestions what I could try or why this is happening for a temp table?