Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Unanswered: Booleans in Sql-statements

    I am working with a function that returns a boolean. I would like to use the function in a standalone sql-statement. It fails because SQL doesn't have the boolean type. Is there an oracle/sql function I can wrap it with to convert the boolean to something sql can use ie a char or number?

    I would like to avoid creating a wrapper function that will execute the existing function and return a char or number.

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713
    You could change the function to return varchar2, something like this:

    create or replace function myboolean(parm1 number)
    return varchar2 is
    begin
    if parm1 > 100 then
    return 'TRUE';
    else
    return 'FALSE';
    end if;
    end;
    /

    And use it like:

    select * from tableA
    where myboolean(123)='TRUE'
    /

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Booleans in Sql-statements

    Originally posted by carloa
    I am working with a function that returns a boolean. I would like to use the function in a standalone sql-statement. It fails because SQL doesn't have the boolean type. Is there an oracle/sql function I can wrap it with to convert the boolean to something sql can use ie a char or number?

    I would like to avoid creating a wrapper function that will execute the existing function and return a char or number.
    Unfortunately not, because in SQL functions cannot have parameters of type BOOLEAN either, so you cannot write:

    SELECT wrapper( boolean_function( ... )) FROM ...;

    If you do you will get:

    ORA-06552: PL/SQL: Statement ignored
    ORA-06553: PLS-382: expression is of wrong type

    So the only answer is to do what you say you don't want to do!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •