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.
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.