Results 1 to 8 of 8

Thread: function in sql

  1. #1
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149

    Unanswered: function in sql

    Hello,

    I have the following function:
    CREATE OR REPLACE FUNCTION ish.applfn_StringsAreEqual
    (
    sValue1 IN VARCHAR2,
    sValue2 IN VARCHAR2
    )
    RETURN BOOLEAN
    AS
    bBOOL BOOLEAN;
    BEGIN
    -- If you have ANSI_NULL on, anytime you compare something to a NULL it will fail.
    -- ANSI_NULL is on so we need to check on NULL values

    bBOOL := FALSE;

    IF ( sValue1 IS NULL AND sValue2 IS NULL ) THEN
    bBOOL := TRUE;
    END IF;

    IF ( sValue1 IS NOT NULL AND sValue2 IS NOT NULL AND sValue1 = sValue2 ) THEN
    bBOOL := TRUE;
    END IF;

    RETURN bBOOL;
    END;
    /

    So Now I want to use this action within a query!
    like this:
    SELECT *
    FROM ISH.TEST
    WHERE applfn_StringsAreEqual( TEST1, TEST2 )
    ;
    It says not a valid relational operator!

    Does anyone know how to solve this? I use more often functions within a query. This is normally no problem.

    thanx,

    Coen Dunnink
    The Netherlands

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    What you are saying is, actually,

    SELECT something FROM a_table
    WHERE TRUE;

    This is not a valid WHERE clause; functions that return Boolean should be used in different manner (for example, in an IF-THEN-ELSE statement), such as this example:
    Code:
    CREATE OR REPLACE FUNCTION is_emp_in_dept (par_empno IN NUMBER, par_deptno IN NUMBER)
    RETURN BOOLEAN
    IS
      -- Does employee work in this department?
    
      l_var EMP.deptno%TYPE;
      retval BOOLEAN;
    BEGIN
      SELECT e.deptno INTO l_var
      FROM EMP e
      WHERE e.empno = par_empno;
      
      IF l_var = par_deptno THEN
         retval := TRUE;   -- yes, he works there
      ELSE
         retval := FALSE;  -- no, he doesn't work there
      END IF;
      
      RETURN (retval);
    END;
    Now you would use this function like this:
    Code:
    BEGIN
      FOR cur_r IN (SELECT e.empno, e.ename FROM EMP e) LOOP
         IF is_emp_in_dept(cur_r.empno, 10)
         THEN
    	    dbms_output.put_line(cur_r.ename ||' works in department 10');
         ELSE
    	    dbms_output.put_line(cur_r.ename ||' does not work in department 10');
         END IF;
      END LOOP;
    END;

  3. #3
    Join Date
    Jan 2004
    Posts
    370
    Why use pl/sql when you can do the same in sql?

    SELECT *
    FROM ISH.TEST
    WHERE nvl( TEST1,'a') = nvl( TEST2,'a' );


    Oops. Posted too soon.
    There is a fatal flaw in this.
    Last edited by SkyWriter; 01-20-06 at 09:16. Reason: Doh

  4. #4
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149

    Incorrect

    What you say can go wrong. What if I have 'a' and NULL. You would say these values are equal?

  5. #5
    Join Date
    Jan 2004
    Posts
    370
    Quote Originally Posted by thepercival
    What you say can go wrong. What if I have 'a' and NULL. You would say these values are equal?
    .
    Yea. You're right. No, they are not equal.


    .

  6. #6
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149

    Thumbs up yeah

    I thought so! ;-)

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Bool can NOT be used in sql, only in sql*plus statements. You can make the function work easly. Rewrite it as

    CREATE OR REPLACE FUNCTION ish.applfn_StringsAreEqual
    (
    sValue1 IN VARCHAR2,
    sValue2 IN VARCHAR2
    )
    RETURN varchar2
    AS
    bBOOL varchar2(1);
    BEGIN
    -- If you have ANSI_NULL on, anytime you compare something to a NULL it will fail.
    -- ANSI_NULL is on so we need to check on NULL values

    bBOOL := 'N';

    IF ( sValue1 IS NULL AND sValue2 IS NULL ) THEN
    bBOOL := 'Y';
    END IF;

    IF ( sValue1 IS NOT NULL AND sValue2 IS NOT NULL AND sValue1 = sValue2 ) THEN
    bBOOL := 'Y';
    END IF;

    RETURN bBOOL;
    END;
    /

    Then your sql would be

    SELECT *
    FROM ISH.TEST
    WHERE applfn_StringsAreEqual( TEST1, TEST2 ) = 'Y';
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149

    Talking Ok

    Yes, exactly!

    I already made that up, after the previous few posts. But thanx anyway because that is the way to do it!!!!

Posting Permissions

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