getting different results from the same query according to the value of a variable

suppose we have the following masterdata:

order_type: [classic, transport]
order_status: [new, active, delivered, finished]

and we have the order tables:

+----------+------------+--------------+
| order_id | order_type | order_status |
+==========+============+==============+
| 1        | classic    | new          |
+----------+------------+--------------+
| 2        | classic    | active       |
+----------+------------+--------------+
| 3        | transport  | active       |
+----------+------------+--------------+
| 4        | transport  | delivered    |
+----------+------------+--------------+
| 5        | classic    | finished     |
+----------+------------+--------------+
| 6        | classic    | active       |
+----------+------------+--------------+
| 7        | transport  | delivered    |
+----------+------------+--------------+
| 8        | classic    | finished     |
+----------+------------+--------------+

what is requested is to have one single query that could return 2 different sets of data, according to the value of a given variable v_order_status:

v_order_status = 'del' then only transport orders is retrieved:

+----------+------------+--------------+
| order_id | order_type | order_status |
+==========+============+==============+
| 3        | transport  | active       |
+----------+------------+--------------+
| 4        | transport  | delivered    |
+----------+------------+--------------+
| 7        | transport  | delivered    |
+----------+------------+--------------+

v_order_status =any other value or null then we retrieve the whole table.

the desired SQL statement is to be used within a stored procedure, something like:

procedure process_orders (v_order_status in varchar2(3) default null)
as
begin
  for ord in (select order_id, order_type, order_status
                from orders
                where [here comes the tricky part])
  loop
    do_something (ord.order_id);
    ...
  end loop;
end process_orders ;

any ideas how to set the where clause in the SQL statement?

Answer

Use boolean logic:

select order_id, order_type, order_status
from orders
where 
    (v_order_status = 'del' and order_type = 'transport')
    or v_order_status <> 'del' 
    or v_order_status is null

Advertisement

Answer