Appearance

Guides>Scripting Guide>Conditional logic

Conditional logics ​

The following scripting constructs for conditional logic are supported by Metakraftwerk:

  • IF ... THEN ... ELSEIF ... ELSE ... END IF;
  • CASE WHEN ... END

IF construct ​

Execute a statement or set of statements if a condition is met.

Syntax ​

sql
IF (cond) THEN 
    statement; -- execute if the "condition 1" is true
    [statement; ...]

[ELSEIF (cond) THEN
    statement -- execute if the "condition 2" is true.
    [statement; ...]
]

[ELSE
    statement -- execute if condition 1 and condition 2 are both not true
    [statement; ...]
]

END IF

Arguments ​

cond: An expression that evaluates to a BOOLEAN.

statement: A single SQL statement (including CALL) or a control-flow statement (for example, a looping or branching statement) or a nested block.

IF cond THEN statement: cond, that must evaluate to true, to execute statement

ELSEIF condition THEN statement(optional): additional conditions.

ELSE statement(optional): statement that is executed if none of the preceding conditions evaluate to true

Note

  • You can use BEGIN and END keywords after each THEN or ELSE (including ELSEIF) clause in the body, even if the body contains multiple statements.
  • NULL will be treated as FALSE as a result of condition evaluation.
  • If multiple cond evaluate to TRUE, only the branch of the first TRUE-cond is executed.

See "IF" statement in "Scripting Reference" for additional information.

Example 1: IF ​

sql
DECLARE
    temperature INTEGER DEFAULT 45;
    decision  STRING DEFAULT '';
    decisions STRING DEFAULT '';
BEGIN
    FOR i IN 1 TO 5 DO
        IF (temperature < 0) THEN
            decision := 'frost';        /* [!code focus] */  
        ELSEIF (temperature < 20) THEN
            decision := 'cold';         
        ELSEIF (temperature = 0) THEN
            decision := '0';            
        ELSEIF (temperature > 30) THEN
            decision := 'hot';          
        ELSE
            decision := 'ok';           
        END IF;                         
        decisions := decisions || 'C ' || temperature::STRING || ' is ' || decision || '+++\n';
        temperature := temperature - (15);
    END FOR;
    RETURN decisions;
END;
+-------------------------------------------------------------------------------+
| anonymous block                                                               |
+-------------------------------------------------------------------------------+
| C 45 is hot+++ C 30 is ok+++ C 15 is cold+++ C 0 is cold+++ C -15 is frost+++ |
+-------------------------------------------------------------------------------+

Note

Result: “C 0 is cold” results from the rule that the first expression that evaluates to TRUE is used.

Example 2: IF in a stored procedure ​

sql
CREATE OR REPLACE PROCEDURE my_folder.feel_prpc(temperature INTEGER)
RETURNS STRING
AS
DECLARE
    decision  STRING DEFAULT '';
    decisions STRING DEFAULT '';
BEGIN
    FOR i IN 1 TO 5 DO
        IF (temperature < 0) THEN
            decision := 'frost';                  
        ELSEIF (temperature between 1 and 20) THEN
            decision := 'cold';                   
        ELSEIF (temperature = 0) THEN
            decision := '0';                      
        ELSEIF (temperature > 30) THEN
            decision := 'hot';                    
        ELSE
            decision := 'ok';                     
        END IF;                                   
        decisions := decisions || 'C ' || temperature::STRING || ' is ' || decision || '+++\n';
        temperature := temperature - (15);
    END FOR;
    RETURN decisions;
END;

Call the stored procedure.

sql
CALL my_folder.feel_prpc(45);

Output:

txt
+----------------------------------------------------------------------------+
| FEEL_PRPC                                                                  |
+----------------------------------------------------------------------------+
| C 45 is hot+++ C 30 is ok+++ C 15 is cold+++ C 0 is 0+++ C -15 is frost+++ |
+----------------------------------------------------------------------------+

Note

Note: In contrast to the example 1, the message changes to "0" by extending the line "...ELSEIF (temperature between 1 and 20) THEN..." with the additional condition, which makes it unique.

CASE construct overview ​

A CASE construct provides a simpler way to specify multiple conditions.

Two forms of the CASE statement are supported by Metakraftwerk:

  • "Simple" CASE statements
  • "Searched" CASE statements

Note

The keyword CASE can also be used outside of Metakraftwerk Scripting (e.g. the conditional expression CASE in the SELECT clause).

See "CASE" in "Scripting Reference" for additional information.

Simple CASE construct ​

Define different WHEN clauses ("branches") for different possible values of a given CASE expression to execute branch's statements.

Syntax ​

sql
CASE expr

    WHEN expr_val THEN
        statement;
        [statement; ...]

    [WHEN expr_val THEN
        statement;
        [statement; ...]
    ]

    ... 
    [ELSE
        statement;
        [statement; ...]
    ]

END [CASE]

Arguments ​

expr: A general expression.

expr_val: Value that must match expr to execute this branch's statements. It can be a literal or an expression. The expr_val must be the same data type as the expr, or must be a data type that can be cast to the data type of the expr.

statement: A single SQL statement (including CALL) or a control-flow statement (for example, a looping or branching statement) or a nested block.

END [CASE]: The keyword CASE in END CASE clause is optional and serves for readability.

Example 3: CASE as Simple construct ​

sql
DECLARE 
    temperature INTEGER;
BEGIN
    temperature := -10;         
       CASE (temperature)                                      
       WHEN -10 THEN
            RETURN 'frost';                                      
       WHEN 10 THEN
            RETURN 'cold';                                      
       WHEN 0 THEN
            RETURN '0';                                      
       WHEN 50 THEN
            RETURN 'hot';                                      
       ELSE
            RETURN 'ok';                                      
       END CASE;                                      
END;

Output:

txt
+-----------------+
| anonymous block |
+-----------------+
|      frost      |
+-----------------+

Searched CASE construct ​

Specify different conditions for each WHEN clause (branch). The first branch for which the expression evaluates to TRUE will be executed.

Syntax ​

sql
CASE

  WHEN cond THEN
    statement;
    [statement; ...]

  [WHEN cond THEN
    statement;
    [statement; ...]
  ]

  ...

  [ELSE
    statement;
    [statement; ...]
  ]

END [CASE]

Arguments ​

cond: An expression that evaluates to a BOOLEAN.

statement: A single SQL statement (including CALL) or a control-flow statement (for example, a looping or branching statement) or a nested block.

END [CASE]: The keyword CASE in END CASE clause is optional and serves for readability.

Note

  • if multiple branchs of the CASE would match the expr, only the first is used.
  • During the comparison, NULL does not match NULL. To explicitly test for NULL values, use the IS [NOT] NULL operator.

Example 4: CASE as Searched Construct ​

sql
DECLARE
    decision  STRING DEFAULT '';
    decisions STRING DEFAULT '';
    temperature INT;
BEGIN
    temperature := 45;         
    FOR i IN 1 TO 5 DO
       CASE
       WHEN (temperature < 0) THEN
            decision := 'frost';                           
       WHEN (temperature between 1 and 20) THEN
            decision := 'cold';                           
       WHEN (temperature = 0) THEN
            decision := '0';                            
       WHEN (temperature >30) THEN
            decision := 'hot';                             
       ELSE
            decision := 'ok';                        
       END;                                      
        decisions := decisions || 'C ' || temperature::STRING || ' is ' || decision || '+++\n';
        temperature := temperature - (15);
    END FOR;
    RETURN decisions;
END;

Output:

txt
+----------------------------------------------------------------------------+
| anonymous block                                                            |
+----------------------------------------------------------------------------+
| C 45 is hot+++ C 30 is ok+++ C 15 is cold+++ C 0 is 0+++ C -15 is frost+++ |
+----------------------------------------------------------------------------+

Note

  • The output of this example is equal to the output of "Example 2"
  • Optional word [CASE] is missing in the END-clause
© 2025 MetaKraftwerk GmbH. All Rights Reserved.