pavan kumar pavan kumar - 10 days ago 5
SQL Question

Can i use case expressions on both sides of the where clause?

Like this

SELECT *
FROM UsersMedicalSurgicalHistory UMSH INNER JOIN CCDTransaction CT on
UMSH.SurgicalHistoryId = CT.RowId
WHERE (
(CASE
WHEN LEN(UMSH.DateOfProcedure)<=4 THEN UMSH.DateOfProcedure
WHEN LEN(UMSH.DateOfProcedure)=0 THEN UMSH.DateOfProcedure
END
=
CASE WHEN @CodeFilter3 IS not null THEN @CodeFilter3
ELSE UMSH.DateOfProcedure end)
OR
(CASE
WHEN LEN(UMSH.DateOfProcedure)>4 THEN
CONVERT(datetime,UMSH.DateOfProcedure,101)
ELSE GETDATE()
END
=
CASE WHEN @CodeFilter2 IS not null THEN @CodeFilter2
ELSE GETDATE()
END)
)


So the question is like CASE expression can be used on both side of WHERE clause

Answer

As per your query is written above...

CASES can be minimized to nested conditions as,

SELECT * FROM UsersMedicalSurgicalHistory UMSH 
INNER JOIN CCDTransaction CT on UMSH.SurgicalHistoryId = CT.RowId  
WHERE (
        (UMSH.DateOfProcedure = @CodeFilter3 and @CodeFilter3 IS not null)
        OR
        @CodeFilter3 IS null
    )
    OR
    (
        (
            (LEN(UMSH.DateOfProcedure)>4 AND CONVERT(datetime,UMSH.DateOfProcedure,101)=@CodeFilter2)
            OR
            (LEN(UMSH.DateOfProcedure)<=4 AND GETDATE() =@CodeFilter2)
            AND @CodeFilter2 IS NOT NULL
        )
        OR
        (LEN(UMSH.DateOfProcedure)<=4 AND @CodeFilter2 IS null)
    )

Try this query... hope it gives desired output :)

Comments