Question: I have a column called `purchase_order_status` in `purchases` table which contains `Open` and `Closed` as values.

```
SELECT *
  FROM purchases
 WHERE purchase_order_status = :status

```

If user passes `All` as bind parameter, how can I fetch all rows having `Open` and `Closed` status like the following using case statement?

```
in ('Open','Closed')

```

Is it possible to use bind variable in case statement, some thing like 

```
case when :status = 'All' then 'Open','Closed'

```

Answer: 
You can use `case` but it's easier and clearer to just use normal logical operators:

```
SELECT *
  FROM purchases
 WHERE ((:status = 'All' AND purchase_order_status IN ('Open','Closed'))
    OR purchase_order_status = :status)

```

With some trivial sample data and an SQL\*Plus bind variable:

```
create table purchases (purchase_order_status varchar2(10));

insert into purchases values ('Open');
insert into purchases values ('Closed');
insert into purchases values ('Pending');

var status varchar2(10);

```

Passing 'Open':

```
exec :status := 'Open';

anonymous block completed

SELECT *
  FROM purchases
 WHERE ((:status = 'All' AND purchase_order_status IN ('Open','Closed'))
    OR purchase_order_status = :status);

PURCHASE_ORDER_STATUS
---------------------
Open                  

```

And passing 'All:

```
exec :status := 'All';

anonymous block completed

SELECT *
  FROM purchases
 WHERE ((:status = 'All' AND purchase_order_status IN ('Open','Closed'))
    OR purchase_order_status = :status);

PURCHASE_ORDER_STATUS
---------------------
Open                  
Closed                

```

If you can guarantee that the column can *only* hold those two values then you can simplify it further to just:

```
SELECT *
  FROM purchases
 WHERE :status = 'All' OR purchase_order_status = :status;

```
Alex answer is better IMO, but here is an alternative. 

```
declare @test as varchar(10)
set @test = 'ALL'
if(@test='ALL') begin
select * from purchases where purchase_order_status in('Open','Closed')
end
if(@test='OPEN') begin
select * from purchases where purchase_order_status in('Open')
end

```

Personally I would do this kind of thing in the code that generates the select queries (if there is such a code)