I'm trying to understand how to use the SQL/PSM LEAVE statement in
conjuction with a LABEL. Can someone please post an example.

I see several references to [<Label>] throughout the manuals but I
could not find one example of an actual [<Label>] being used.

Thanks in advance.

--
Michael Riley
GySgt USMC (Ret)
www.zilchworks.com

Figured it out. Here is a function that returns Numeric values formated
with a thousands separator and number of decimal places.

Each Label tests the input parameter "Decimals" and executes a LEAVE
statement if that statement block is not the appropriate set of code.


CREATE FUNCTION "fFormatThousands" (INOUT "Val1" DECIMAL(19,4),INOUT
"Decimals" INTEGER)
RETURNS VARCHAR(20) COLLATE UNI_WI
BEGIN

DECLARE Str1 VARCHAR(30);
DECLARE Str2 VARCHAR(30);

DECLARE StrLength  INTEGER;
DECLARE ZeroPad    INTEGER;
DECLARE DecimalPos INTEGER;

DECLARE i INTEGER;
DECLARE j INTEGER;

SET Str1     = '';
SET Str2     = '';
SET ZeroPad  = 0;

CASE DECIMALS
 WHEN 0 THEN SET Val1 = ROUND(Val1 to 0);
 WHEN 1 THEN SET Val1 = ROUND(Val1 to 1);
 WHEN 2 THEN SET Val1 = ROUND(Val1 to 2);
 WHEN 3 THEN SET Val1 = ROUND(Val1 to 3);
 WHEN 4 THEN SET Val1 = ROUND(Val1 to 4);
 ELSE
   BEGIN
     SET Val1 = ROUND(Val1 to 2);
     SET Decimals = 2;
   END;
END CASE;
SET Str1 = CAST(Val1 as VARCHAR);

----------------------------------------------------------------
ProcessZeroDecimals:
----------------------------------------------------------------
BEGIN
 IF DECIMALS <> 0 THEN LEAVE ProcessZeroDecimals; END IF;
 SET StrLength = LENGTH(Str1);
 SET Str2 = '';
 SET i = 0;
 SET j = StrLength;
 WHILE j > 0 DO
   SET Str2 = SUBSTRING(Str1, j for 1) + Str2;
   SET i = i + 1;
   SET j = j - 1;
   IF (i MOD 3 = 0) AND (j > 0) THEN
     SET Str2 = ',' + Str2;
   END IF;
 END WHILE;
----------------------------------------------------------------
END;--ProcessZeroDecimals:
----------------------------------------------------------------

----------------------------------------------------------------
ProcessNonZeroDecimals:
----------------------------------------------------------------
BEGIN
 IF DECIMALS = 0 THEN LEAVE ProcessNonZeroDecimals; END IF;
 SET DecimalPos = POSITION('.' IN Str1);
 IF DecimalPos = 0 then
   CASE Decimals
     WHEN 1 THEN SET Str1 = Str1 + '.0';
     WHEN 2 THEN SET Str1 = Str1 + '.00';
     WHEN 3 THEN SET Str1 = Str1 + '.000';
     WHEN 4 THEN SET Str1 = Str1 + '.00000';
   END Case;
 END IF;
 SET StrLength = LENGTH(Str1);
 SET DecimalPos = POSITION('.' IN Str1);
 SET ZeroPad = Decimals - (StrLength - DecimalPos);
 SET Str2 = SUBSTRING(Str1, DecimalPos for Decimals+1);
 SET i = 0;
 SET j = DecimalPos -1;
 WHILE j > 0 DO
   SET Str2 = SUBSTRING(Str1, j for 1) + Str2;
   SET i = i + 1;
   SET j = j - 1;
   IF (i MOD 3 = 0) AND (j > 0) THEN
     SET Str2 = ',' + Str2;
   END IF;
 END WHILE;
 CASE ZeroPad
   WHEN 1 THEN SET Str2 = Str2 + '0';
   WHEN 2 THEN SET Str2 = Str2 + '00';
   WHEN 3 THEN SET Str2 = Str2 + '000';
   WHEN 4 THEN SET Str2 = Str2 + '0000';
 END CASE;
----------------------------------------------------------------
END; --ProcessNonZeroDecimals:
----------------------------------------------------------------

RETURN str2;

END
VERSION 1.00!



--
Michael Riley
GySgt USMC (Ret)
www.zilchworks.com

Thank you Michael. I'm not at all sure I would have figured out the use of LEAVE without this. It is similar to BREAK or EXIT in Delphi ...

Adam Brett wrote:

> Thank you Michael. I'm not at all sure I would have figured out the
> use of LEAVE without this. It is similar to BREAK or EXIT in Delphi
> ...

You are welcome Adam.

--
Michael Riley
GySgt USMC (Ret)
www.zilchworks.com