I have this big code where I want 3 things in my search:
1- look for all the orders (delivered and not) that match the search:
2- look for all the pendent orders that match the search;
3- look for all the delivered orders that match the search;
create or replace
function search_order(search IN VARCHAR2, a_option NUMBER) RETURN types.ref_cursor
AS
orders_cursor types.ref_cursor;
BEGIN
if search is not null then
if a_option = 0 then /*case 1*/
OPEN orders_cursor FOR
select value(f), value(p),i.qtd_if, i.prec_total_if , forn.nome_fornecedor
from item_fornecimento i, produto p ,fornecimento f, fornecedor forn
where f.id_fornecimento in (select f.id_fornecimento from fornecimento f where f.id_fornecedor in
(select f1.id_fornecedor from fornecedor f1 where f1.nome_fornecedor LIKE % ||search|| % ))
and f.id_fornecimento= i.id_fornecimento and i.id_prod= p.id_prod and
f.id_fornecedor = forn.id_fornecedor
order by forn.nome_fornecedor,f.data_encomenda desc,p.nome_prod asc;
RETURN orders_cursor;
ELSIF a_option = 1 then /*case 2*/
OPEN orders_cursor FOR
(...)
where f.id_fornecimento in (select f.id_fornecimento from fornecimento f where f.id_fornecedor in
(select f1.id_fornecedor from fornecedor f1 where f1.nome_fornecedor LIKE % ||search|| % )and f.data_entrega is null)
(...)
RETURN orders_cursor;
ELSE /* case 3*/
OPEN orders_cursor FOR
(...)
where f.id_fornecimento in (select f.id_fornecimento from fornecimento f where f.id_fornecedor in
(select f1.id_fornecedor from fornecedor f1 where f1.nome_fornecedor LIKE % ||search|| % )and f.data_entrega is not null)
(...)
RETURN orders_cursor;
end if;
end if;
END;
This works if my search is not null, but if it is I would like just to modify a little bit the inner select and turn it into something like this:
(select f1.id_fornecedor from fornecedor f1 where f1.nome_fornecedor LIKE % ||search|| % )and f.data_entrega is not null) to --> (select f1.id_fornecedor from fornecedor f1)and f.data_entrega is not null)
So I have 3 conditions for search, and I would like to know if it s possible to use something like case, decode or even another cursor with a parameter, to do this inner select with:
- LIKE if the search string is not null;
- without LIKE, if the string is null;
But I haven t seen any examples of this and things can really go quite messy. Could someone help a newbie with same code?