Control Structures

You are looking at an older version of the documentation. The latest version is found here.

IF Statement

An IF statement evaluates a condition and executes one of two statements depending on the result of the evaluation. You can nest IF statements to create complex branching logic. A dependent ELSE statement will be executed only if the IF statement evaluates to false.

Usage

IF (criteria)
    block
[ELSE
    block]

Example

BEGIN
DECLARE STRING var1 = 'South America';
IF (var1 = 'North America')
    BEGIN
        ...
    END
ELSE
    BEGIN
        ...
    END
END

NULL values should be considered in the criteria of an IF statement. IS NULL can be used to detect the presence of a NULL value.

Loop Statement

A LOOP statement is an iterative control construct that is used to cursor through a result set.

Usage

[label :] LOOP ON (<select statement>) AS <cursorname>
    block

Syntax

• The label must not be the same as any other label used in statements containing this one.

Example

BEGIN 
	LOOP ON (SELECT id FROM "SYSADMIN.ScheduleJobs") AS JOB 
	BEGIN 
		CALL SYSADMIN.createSchedule(jobId => job.id, type => 'once', intervl => 0, startDelay => 0, enabled => true); 
	END 
END;;

While Statement

A WHILE statement is an iterative control construct that is used to execute a block repeatedly whenever a specified condition is met.

Usage

[label :] WHILE <criteria>
    block

Syntax

• The label must not be the same as any other label used in statements containing this one.

Example

BEGIN 
	DECLARE integer i = 0; 
	WHILE (i < 5) BEGIN 
		SELECT i; i = i + 1; 
	END 
END ;;

Continue Statement

A CONTINUE statement is used inside a LOOP or WHILE construct to continue with the next loop by skipping over the rest of the statements in the loop. It must be used inside a LOOP or WHILE statement.

Usage

CONTINUE [label];

Syntax

• If the label is specified, it must exist on a containing LOOP or WHILE statement;
• If no label is specified, the statement will affect the closest containing LOOP or WHILE statement.

Example

BEGIN 
	DECLARE integer i = 0; 
	DECLARE string str = 0; 
	WHILE (i < 5) 
		BEGIN 
			i = i + 1; 
			IF (i in (2, 3)) 
				CONTINUE; 
			str = str || i; 
		END 
	SELECT str; 
END ;;

Break Statement

A BREAK statement is used inside a LOOP or WHILE construct to break from the loop. It must be used inside a LOOP or WHILE statement.

Usage

BREAK [label];

Syntax

• If the label is specified, it must exist on a containing LOOP or WHILE statement;
• If no label is specified, the statement will affect the closest containing LOOP or WHILE statement.

Example

BEGIN 
	DECLARE integer i = 0; 
	WHILE (i < 5) BEGIN 
		IF(i = 3) 
			BREAK; 
		SELECT i; 
		i = i + 1; 
	END
END ;;

Leave Statement

A LEAVE statement is used inside a compound, LOOP, or WHILE constructs to leave to the specified level.

Usage

LEAVE label;

Syntax

• The label must exist on a containing compound statement, LOOP, or WHILE statement.

Example

BEGIN 
	DECLARE string str = 0; 
	LABEL_A: BEGIN 
			BEGIN 
				str = 'a'; 
				LEAVE LABEL_A; 
			END 
			str = 'b'; 
		END 
	SELECT str; 
END ;;