I can't use boolean in a sql query. Therefore I can't create a function that return true or false and use it to test a condition. I must create a function that return something (1 for instance) and test it. Like that:

   WITH
    FUNCTION f (input INTEGER)
        RETURN INTEGER
    IS
    BEGIN
        RETURN CASE WHEN input = 1 THEN 1 ELSE 0 END;
    END;

    A AS (SELECT 1 a FROM DUAL)
SELECT *
  FROM a
 WHERE f(a.a) = 1

instead of that:

WITH
    FUNCTION f (input INTEGER)
        RETURN boolean
    IS
    BEGIN
        RETURN  input = 1 ;
    END;

    A AS (SELECT 1 a FROM DUAL)
SELECT *
  FROM a
 WHERE f(a.a) 

Unless there is another way?

code

I've tried to use a macro but to no avail

WITH
 FUNCTION ft 
        RETURN VARCHAR2 SQL_MACRO
    IS
    BEGIN
        RETURN q'{
      SELECT 1 
   FROM dual
  }';
    END;
    
 FUNCTION fc 
        RETURN VARCHAR2 SQL_MACRO
    IS
    BEGIN
        RETURN q'{
      1=1
  }';
    END;
SELECT *
  FROM ft()
 WHERE fc()

ORA-00920: invalid relational operator

code

CodePudding user response：

The BOOLEAN data type is a PL/SQL only data type and is not supported in Oracle SQL statements.

Use two constants:

• 0 for false, 1 (or non-zero) for true (as per the C language).
• 0 for no errors, non-zero for errors (as per Unix program exit codes).
• 'Y' for yes, 'N' for no.
• 'success' and 'failure'
• etc.

Whatever you return is your personal preference. Document the convention you are going to use and then use it consistently so that everyone on the same project uses the same convention.

Unless there is another way?

No, just pick a convention for truthy/falsy values and stick to that.

CodePudding user response：

WITH
    FUNCTION f_check_int ( p_str VARCHAR2 )
         RETURN VARCHAR2  ------- Y / N
IS
   lv_data NUMBER;
BEGIN
   lv_data := TO_NUMBER(p_str);
   IF lv_data>0 AND MOD(lv_data,1)=0 THEN
      RETURN 'Y';
   ELSE
      RETURN 'N';
   END IF;
EXCEPTION
   WHEN VALUE_ERROR THEN
        RETURN 'N';
END;

A AS (SELECT 1 a FROM DUAL)
SELECT *
  FROM a
 WHERE f_check_int(a.a) = 'Y'