Function in Oracle SQL where clause

Question

I have a query like this:

SELECT * 
FROM employees emp 
WHERE emp.loc = 'US' 
  AND usr.fnctn('emp_sal', :sal1, :sal2);

This query causes an error:

ORA-00920: invalid RELATIONAL OPERATOR

My function just returns the highest and the lowest sal in employees table

You can not use boolean inside of sql, so fnctn must return something else and therefore you need some relation to compare the return value with. — Radagast81
– Radagast81, Commented Sep 9, 2019 at 7:14
Your function returns two values? Not possible except it returns collection. — Popeye
– Popeye, Commented Sep 9, 2019 at 7:18
"docs.oracle.com/cd/B28359_01/appdev.111/b28370/…" - "Avoid using OUT and IN OUT with functions. The purpose of a function is to take zero or more parameters and return a single value. Functions must be free from side effects, which change the values of variables not local to the subprogram." — ArtBajji
– ArtBajji, Commented Sep 9, 2019 at 8:19

user330315 · Accepted Answer · 2019-09-09 06:46:45Z

3

A function returns a value.

In the where clause you need to compare that return value with something, e.g.

SELECT * 
FROM employees emp 
WHERE emp.loc = 'US' 
AND usr.fnctn('emp_sal', :sal1, :sal2) = 42;

answered Sep 9, 2019 at 6:46

user330315

Sign up to request clarification or add additional context in comments.

1 Answer 1