IF statement

The IF statement can be used instead of the SEARCHED-CASE statement. With the IF statement it’s possible to enter into some logic based on the status of the IF condition.

You’ve to specifiy one IF-THEN condition and one ELSE clause. Optionally there are one ore multiple ELSEIF clauses possible. Note that ELSEIF is one word and don’t forget the END IF clause to to specifiy the end of the IF statement.

Example 1:

create procedure myproc (in v_var01 integer, out v_var02 varchar(50))
 begin
    if v_var01 < 0 then
      set v_var02='Variable is lower then 0';

    elseif v_var01 >= 0 and v_var01 < 10 then
      set v_var02='Variable is between 0 and 9';

    elseif v_var01 >= 10 and v_var01 < 20 then
      set v_var02='Variable is between 10 and 19';

    else
      set v_var02='Variable is 20 or higher';

    end if;
 end!

Example 2:

This example shows an User Defined Function written in Inline SQL PL:

create function convert_to_eur(value decimal(6,2), currency varchar(3))
returns decimal(6,2)
language sql
deterministic
contains sql
begin atomic

  declare output decimal(6,2);

  if currency = 'DEM' then
     set output = value/1.95583;
  elseif currency = 'BEF' then
     set output = value/40.3399;
  elseif currency = 'FIM' then
     set output = value/5.94573;
  elseif currency = 'FRF' then
     set output = value/6.55957;
  elseif currency = 'GRD' then
     set output = value/340.750;
  elseif currency = 'IEP' then
     set output = value/0.787564;
  elseif currency = 'ITL' then
     set output = value/1936.27;
  elseif currency = 'LUF' then
     set output = value/40.3399;
  elseif currency = 'MTL' then
     set output = value/0.429300;
  elseif currency = 'NLG' then
     set output = value/2.20371;
  elseif currency = 'ATS' then
     set output = value/13.7603;
  elseif currency = 'PTE' then
     set output = value/200.482;
  elseif currency = 'SIT' then
     set output = value/239.640;
  elseif currency = 'ESP' then
     set output = value/166.386;
  elseif currency = 'CYP' then
     set output = value/0.585274;
  end if;

return output;
end!

Link to the DB2 Information Center: DB2 9.7 DB2 10.1 DB2 10.5 DB2 11.1