I wrote this oracle procedure to return a rows of data. In the case, I will check if the Operator is equal, greater than or less than. If so, then use b.rating = p_rating or b.rating > p_rating or b.rating < p_rating, correspondingly in the SQL.
However, Oracle complains that next to the equal sign, there is a "missing keyword".
procedure test(
p_rc out sys_refcursor,
p_rating in varchar2,
p_operator in varchar2,
...
)
is
begin
open p_rc for
select
*
from
tableA a, tableB b
where a.id=b.id and
(case
when p_operator = 'EQ' then b.rating = p_rating
when p_operator = 'GT' then b.rating > p_rating
when p_operator = 'LT' then b.rating < p_rating
end)
...
end;
How to achieve this goal?
Thanks.
Scott.
P.S.
If hard coded, it will be:
For p_operator = 'EQ',
select
*
from
tableA a, tableB b
where a.id=b.id and b.rating = p_rating;
For p_operator = 'GT',
select
*
from
tableA a, tableB b
where a.id=b.id and b.rating > p_rating
For p_operator = 'LT',
select
*
from
tableA a, tableB b
where a.id=b.id and b.rating < p_rating