adding conditional prompt to Cognos 10 sql based query

Question

Cognos BI version: 10.2.2 using report studio
I have a report with one prompt p1 and its a sql query based report.
Prompt UI: p1 is a DropdownList(use value, display value)
1-All, 2-value2, 3-value3, 4-value3

I am trying to add condition based prompt syntax the sql query..

select col1, col2, col3, col4 from table1
where col4  is not null
case when #Prompt('p1')# = 1 then ' and 1=1'
     when #Prompt('p1')# = 2 then ' and col1 is null and col2 is not null'
     when #Prompt('p1')# = 3 then ' and col1 is not null and col2 is null'
     when #Prompt('p1')# = 4 then ' and col1 is null and col2 is null'
end

I tried to use lot of syntax searching online but couldn't find the examples with respect to my scenario. I need help in achieving the correct way of writing the statements to satisfy my requirement.any help is appreciated..

Answer 1

Normally I suggest you post the error but this one seems obvious

select col1, col2, col3, col4 from table1
where col4  is not null
and (
(#Prompt('p1','token','0')# = 1) OR
(#Prompt('p1','token','0')# = 2 and col1 is null and col2 is not null) OR
(#Prompt('p1','token','0')# = 3 and col1 is not null and col2 is null) OR
(#Prompt('p1','token','0')# = 4 and col1 is null and col2 is null)
)