0

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

closed as off-topic by Philᵀᴹ, Michael Green, Tom V, dezso, Colin 't Hart Nov 18 '15 at 13:04

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Philᵀᴹ, Michael Green, Tom V, Colin 't Hart
If this question can be reworded to fit the rules in the help center, please edit the question.

2

I found the solution.

I replaced the = (equal signs) to IN.

The WHERE clause now looks like this:

 WHERE 
    (END_DATE IS NULL OR END_DATE > @REPORT_DATE)
    AND 
    REPORT_SPOOL.FUND_ID IN (SELECT FUND_ID FROM FUND_HLD WHERE [DATE] = @REPORT_DATE)
    AND
    REPORT_SPOOL.FUND_ID IN (SELECT FUND_ID FROM FUND_TRD_LINE_VIEW WHERE [DATE] >= (SELECT DATEFROMPARTS(YEAR(@REPORT_DATE),MONTH(@REPORT_DATE),1)) AND [DATE] <= @REPORT_DATE)

Not the answer you're looking for? Browse other questions tagged or ask your own question.