IBM PureData System for Analytics, Version 7.1

Conditional control

You use IF statements to take action based on certain conditions. NZPLSQL has three forms of IF statements:

IF-THEN
IF-THEN-ELSE
IF-THEN-ELSE IF

All NZPLSQL IF statements require a corresponding END IF statement. In ELSE-IF statements, you need two END IF statements: one for the first IF and one for the second (ELSE IF).

IF-THEN statements

IF-THEN statements are the simplest form of an IF statement. The statements between THEN and END IF are executed if the condition is true. Otherwise, the statements that follow the END IF are executed. An example follows:

IF v_user_id <> 0 THEN
    UPDATE users SET email = v_email WHERE user_id = v_user_id;
END IF;

IF-THEN-ELSE statements

IF-THEN-ELSE statements add an ELSE branch for cases when the IF-THEN condition evaluates to FALSE. You can specify the statements to run in the ELSE section. For example:

IF parentid IS NULL or parentid = ''
THEN
    return fullname;
ELSE
    return hp_true_filename(parentid) || '/' || fullname;
END IF;

IF v_count > 0 THEN
    INSERT INTO users_count(count) VALUES(v_count);
    return 't';
ELSE
    return 'f';
END IF;

You can nest IF statements as in the following example:

IF movies.genre = 'd' THEN
    film_genre := 'drama';
ELSE 
    IF movies.genre = 'c' THEN
        film_genre := 'comedy';
    END IF;
END IF;

IF-THEN-ELSE IF statements

When you use the "ELSE IF" statement, you are nesting an IF statement inside the ELSE statement. Thus, you need one END IF statement for each nested IF and one for the parent IF-ELSE. For example:

IF movies.genre = 'd' THEN
    film_genre := 'drama';
ELSE IF movies.genre = 'c' THEN
        film_genre := 'comedy';
    END IF;
END IF;

While this form works, it can become a little tedious and error-prone if there are many alternatives to check. Thus, the language offers the alternative syntax by using ELSIF or ELSEIF, as follows:

IF movies.genre = 'd' THEN
    film_genre := 'drama';
ELSIF movies.genre = 'c' THEN
        film_genre := 'comedy';
ELSIF movies.genre = 'a' THEN
        film_genre := 'action';
ELSIF movies.genre = 'n' THEN
        film_genre := 'narrative';
ELSE 
-- An uncategorized genre form has been requested.
   film_genre := 'Uncategorized';
END IF;

The IF-THEN-ELSIF-ELSE form offers some flexibility and eases the coding process when you need to check many alternatives in one statement. While it is equivalent to nested IF-THEN-ELSE-IF-THEN commands, it needs only one END IF statement.