Nested case statements in inner joins

Question

Having issues with that I think can be solved by a case statement, but it keeps rejecting my sql. Basically, I'm introducing a new parameter to existing SQL and need to react different ways depending on what it is... sounded like a case statement to me, but it keeps rejecting it. Any thoughts?

Original

INNER JOIN T_CASE CF ON
(
    A.AWESOME_ID = CF.AWESOME_ID    
    // Several more awesome conditions
)
AND
(
    (
        cy.SOMEFLAG = 0
        AND CF.SOMEDATE IS NOT NULL
    )
    OR
    (
        cy.SOMEFLAG = 1
        AND CF.CASE_TYPE = 1
        AND CF.SOMEDATE IS NOT NULL
    )
)

Attempt #1

INNER JOIN T_CASE CF ON
(
    A.AWESOME_ID = CF.AWESOME_ID    
    // Several more conditions
)
AND
(
    // Just this part is what the new parameter applies to
    CASE WHEN @NEWPARAM = 1 THEN
    (
        (
            cy.SOMEFLAG = 0
            AND CF.SOMEDATE IS NOT NULL
        )
        OR
        (
            cy.SOMEFLAG = 1
            AND CF.CASE_TYPE = 1
            AND CF.SOMEDATE IS NOT NULL
        )
    )
    ELSE
    (
        (
            cy.SOMEFLAG = 0
            AND CF.SOMEDATE IS NULL
        )
        OR
        (
            cy.SOMEFLAG = 1
            AND CF.CASE_TYPE = 0
            OR CF.SOMEDATE IS NULL
        )
    )
    END
)

Answer 1

You can't use the case statement to dynamically include/exclude portions of the SQL statement, but you can still get a similar effect like this

INNER JOIN T_CASE CF ON
(
    A.AWESOME_ID = CF.AWESOME_ID    
    // Several more conditions
)
AND
(
    // Just this part is what the new parameter applies to
    1 = CASE WHEN 
        @NEWPARAM = 1 
        and 
        (
            cy.SOMEFLAG = 0
            AND CF.SOMEDATE IS NOT NULL
        )
        OR
        (
            cy.SOMEFLAG = 1
            AND CF.CASE_TYPE = 1
            AND CF.SOMEDATE IS NOT NULL
        )
        THEN 1
    CASE WHEN 
        @NEWPARAM <> 1 
        and
        (
            (
                cy.SOMEFLAG = 0
                AND CF.SOMEDATE IS NULL
            )
            OR
            (
                cy.SOMEFLAG = 1
                AND CF.CASE_TYPE = 0
                OR CF.SOMEDATE IS NULL
            )
        )
        THEN 1
    ELSE 0
    END
)

Basically we're returning 1 for rows that meet the criteria, and 0 for rows that don't and comparing that against the value 1.

Answer 2

In general, I think it is a better idea to use basic logical constructs in condition clauses, rather than the case. You can readily do this:

INNER JOIN T_CASE CF ON
(
    A.AWESOME_ID = CF.AWESOME_ID    
    // Several more conditions
)
AND
(
        (
            @NEWPARAM = 1 AND
            cy.SOMEFLAG = 0 AND
            CF.SOMEDATE IS NOT NULL
        )
        OR
        (
            @NEWPARAM = 1 AND
            cy.SOMEFLAG = 1 AND
            CF.CASE_TYPE = 1 AND
            CF.SOMEDATE IS NOT NULL
        )
        OR
        (
            @NEWPARAM <> 1 AND
            cy.SOMEFLAG = 0 AND
            CF.SOMEDATE IS NULL
        )
        OR
        (
            @NEWPARAM <> 1 AND
            ((cy.SOMEFLAG = 1 AND CF.CASE_TYPE = 0) OR
             CF.SOMEDATE IS NULL
            )
        )
    )