IF with SELECT COUNT(*) Query - IBM DB2 to Oracle Migration

DB2 allows you to use a SELECT statement returning a scalar value, for example SELECT COUNT(*), in the IF statement. The result of the query can be used in a boolean expression.

DB2:

  --#SET TERMINATOR @
 
  CREATE OR REPLACE FUNCTION fn_color_exists(p_name VARCHAR(70))
    RETURNS CHAR(1)
  BEGIN
  -- Execute the query and compare the count with 0 in IF statement 
  IF (SELECT COUNT(*) 
       FROM colors 
       WHERE name = p_name 
       FETCH FIRST ROW ONLY) > 0 
  THEN 
    RETURN 'Y';
  END IF;
 
  RETURN 'N';
 
  END@
  #  DB20000I  The SQL command completed successfully.

In Oracle PL/SQL you cannot use a SELECT statement in the IF statement, so you have to execute the query as a standalone statement.

Oracle:

  CREATE OR REPLACE FUNCTION fn_color_exists(p_name VARCHAR2)
    RETURN CHAR 
  IS
    v_cnt NUMBER(10);
  BEGIN   
   -- Execute the query as a standalone statement
   SELECT COUNT(*) INTO v_cnt 
   FROM colors 
   WHERE name = p_name 
   AND rownum <= 1;
 
  -- Now use the result in IF statement
  IF v_cnt > 0 
  THEN 
    RETURN 'Y';
  END IF;
 
  RETURN 'N';
 
  END;
  /
  # Function created.

Database and SQL Migration Tools

About SQLines

SQLines offers services and tools to help you migrate databases and applications. For more information, please contact us at support@sqlines.com.

Written by Dmitry Tolpeko, dmtolpeko@sqlines.com - September 2013.

You could leave a comment if you were logged in.