Recently I have come up with a problem where I have to write a query against a CTE result. The problem statement looks simple, came to know the complexity only after when I tried it out.
Here is the problem Statement:
There are result set which will come from CTE having two columns named "Col1" and "Col2" which are of type "BIT". At any point of time both "Col1" and "Col2" will not have "1" in any case. Rest all possible combinations can exist.
Problem: The User should have option to see
- Option 1(@None) : all the records with Col1=0 AND Col2=0
- Option 2(@Col1). records with Col1=1. Ex : Col1=1 and Col2 != 1
- Option 3(@Col2). Records with Col2=1. Ex : Col1 != 1 and Col2 = 1
Here User even can see records with multiple combinations.
ex: Option 1 and 2
Can See all records having
- Col1=0 AND Col2=0
- Col1=1 AND Col2=0
Approach:
To achieve this I have tried with various conditions which ended up with the large set of AND, OR opertaions, which looks very difficult to understand and maintain later on time for anyone.
After trying and doing lot of possible ways ended up using "CASE WHEN THEN" in WHERE.
Solution:
Here is how I have achieved :
select * from CndTrk
WHERE
(CASE
when (@Col1=1 AND @Col2=1 AND @None = 1)
THEN 1
WHEN @None =1
THEN
CASE
WHEN @Col2=1 AND CndTrk.Col1 <>1
THEN 1
WHEN @Col1=1 AND CndTrk.Col2 <>1
THEN 1
WHEN CndTrk.Col2 <>1 AND CndTrk.Col1 <>1
THEN 1
END
ELSE
CASE
WHEN @Col2=1 AND CndTrk.Col2 = 1
THEN 1
WHEN @Col1=1 AND CndTrk.Col1 = 1
THEN 1
END
END
) = 1
