Use a boolean return in a where clause 1

[userMikeD]

Hi,

I have a package that has a function and it's return type is BOOLEAN. In pl/sql, I'm able to say:

IF (pricing.does_qualify(p_profile_id)) THEN
....
END IF;

However, I can't seem to get it working in a select statement.
For instance, I've tried to evaluate the function the following ways and they all return an error:

Select 1 FROM dual where (pricing.does_qualify(p_profile_id));

Select 1 FROM dual where pricing.does_qualify(p_profile_id) = TRUE;

Select 1 FROM dual where pricing.does_qualify(p_profile_id) = 1;

Is it possible to put a custom function call like this inside a SQL statement?

Thanks!

 

[SantaMufasa]

Mike,

Strangely, (as you have discovered) Oracle SQL is not prepared to evaluate strictly boolean expressions in WHERE clauses:

create or replace function does_qualify (prof_id number) return boolean is
begin
   if prof_id > 0 then
       return true;
   else
       return false;
   end if;
end;
/

Function created.

select 'positive' from dual where does_qualify(1);
                                                *
ERROR at line 1:
ORA-00920: invalid relational operator

But if you have the luxury of modifying the user-defined function, then you produce the results you want:

create or replace function does_qualify (prof_id number) return varchar2 is
begin
   if prof_id > 0 then
       return 'true';
   else
       return 'false';
   end if;
end;
/

Function created.

select 'positive' from dual where does_qualify(1) = 'true';

'POSITIV
--------
positive

1 row selected.

Let us know if this is satisfactory.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[userMikeD]

Thanks for the reply! I wanted to make sure I wasn't crazy and totally missing something. I can't modify that function to return a string since it's used by many different apps, but I'll just create a wrapper function around it to return 'true' or 'false', and then I can use it in the SELECT. Thanks for help!