I have a SQL Stored Procedure that is giving issues. I am aware that it is most likely the SELECT statements in the WHERE clause that are returning multiple values. I commented out the WHERE and results were returned.
My question is how do I modify the statement so that I can still filter on those conditions?
Stored Proc code:
SELECT REPORT_SPOOL.ID, REPORT_SPOOL.REPORT_SPOOL_TYPE_ID, REPORT_SPOOL.FUND_ID, REPORT_SPOOL.PERF_ENTITY_ID, REPORT_SPOOL.REPORT_GUID, REPORT_SPOOL.REPORT_TEMPLATE_GUID, PERF_ENTITY.CODE AS PERF_ENTITY_CODE, PERF_ENTITY.NAME AS PERF_ENTITY_NAME, FUND.CODE AS FUND_CODE, FUND.NAME AS FUND_NAME, REPORT.CODE AS REPORT_CODE, REPORT.NAME AS REPORT_NAME, REPORT_TEMPLATE.CODE AS REPORT_TEMPLATE_CODE, REPORT_TEMPLATE.NAME AS REPORT_TEMPLATE_NAME, FUND.ACCOUNTING_START AS START_DATE, FUND.ACCOUNTING_END AS END_DATE FROM PERF_ENTITY RIGHT OUTER JOIN REPORT INNER JOIN REPORT_SPOOL ON REPORT.GUID = REPORT_SPOOL.REPORT_GUID INNER JOIN REPORT_TEMPLATE ON REPORT_SPOOL.REPORT_TEMPLATE_GUID = REPORT_TEMPLATE.GUID ON PERF_ENTITY.ID = REPORT_SPOOL.PERF_ENTITY_ID LEFT OUTER JOIN FUND ON REPORT_SPOOL.FUND_ID = FUND.ID WHERE (END_DATE IS NULL OR END_DATE > @REPORT_DATE) AND REPORT_SPOOL.FUND_ID = (SELECT FUND_ID FROM FUND_HLD WHERE [DATE] = @REPORT_DATE) AND REPORT_SPOOL.FUND_ID = (SELECT FUND_ID FROM FUND_TRD_LINE_VIEW WHERE [DATE] >= (SELECT DATEFROMPARTS(YEAR(@REPORT_DATE),MONTH(@REPORT_DATE),1)) AND [DATE] <= @REPORT_DATE)
Those last 2 SELECTS are the issue