Query problem

[jmodic]

Originally posted by dba_admin
How did you find out, even with an inline view, it still run twice for any row returned?

Quite simply, I created a dummy function with DBMS_OUTPUT cals in it and observed how it behaves.

CREATE OR REPLACE FUNCTION foo (p NUMBER) RETURN VARCHAR2
IS
BEGIN
  dbms_output.put_line('EnterED function');
  IF p = 0 THEN
    dbms_output.put_line('Returned NULL');
    RETURN NULL;
  ELSE
    dbms_output.put_line('Returned NOT NULL');
    RETURN 'x';
  END IF; 
END;
/

SET SERVEROUTPUT ON

SELECT c1, xyz FROM
(SELECT c1, foo(c1) xyz FROM t1)
WHERE xyz IS NOT NULL;