How to use CASE statement and a parameter in the WHERE clause?
<p>I have an SSRS report where there is a parameter that asks the user to include records where revenue is greater than zero, or records with revenue values that are just zero.</p>

<p>Since the query is not a stored procedure and it is not an option to put it into a procedure, I need to use some case logic for the embedded query. I need to do this in the where clause in the end. </p>

<p>I am trying to do something like this: </p>

<pre><code>SELECT * FROM TABLE
WHERE MY_DATE BETWEEN D_START AND D_END
AND 
    CASE 
        WHEN :REVENUE = 1 THEN REV != 0
        WHEN :REVENUE = 2 THEN REV = 0
    END 
</code></pre>

<p>However, when I run this query I get the following error: </p>

<pre><code>ORA-00905: missing keyword
</code></pre>

<p>Is what I am doing not possible? Or is there an error that someone can see and help me with?</p>

<p>Please help. Thanks!</p>

<p><strong>UPDATE:</strong> Just to clarify, the user is passing a value of 1 or 2. And the query should filter the data according to what value is passed to it. If 1 is passed in the parameter, then filter out all revenue not equal to zero. Else if two is passed, then filter so that only records where revenue is zero is returned. </p>