Format Numbers with an ElevateDB Function

There are times when I want numeric data coming out of a database formatted with a thousands separator and set number of decimal places. I'd rather see large numbers formatted as 123,740.00 rather than 123740. To me formatted numbers are just easier to read. Unfortunately, ElevateDB does not have a built in function similar to Microsoft's CONVERT.

However, you can create your own CONVERT function within ElevateDB quite easily. ElevateDB has two statements that make the job of formating numbers fairly easy. The first is the LABEL statement and the second is the LEAVE statement. The LABEL statement lets you set up a block of code. For example:

MyFistLabel:
BEGIN
  Do Something1;
  Do Something2;
  Do Something3;
  Do Something4;
  Do Something5;
END; --MyFistLabel

The LEAVE statement lets you exit a block of code. For Example:

MyFistLabel:
BEGIN
  Do Something1;
  Do Something2;
  Do Something3;
  IF Something3 = Done THEN
    LEAVE MyFistLabel
  END IF;
  Do Something4;
  Do Something5;
END; --MyFistLabel

So let's set out to create a function that formats numbers. This function will have two input parameters Value and Decimals. Value is the number to be formatted and Decimals is the number of decimal places. I built this function using two labels. One label that handles zero decimal places and one label that handles non zero decimal places. Here is the stubbed out concept:

----------------------------------------------------------------
ProcessZeroDecimals:
----------------------------------------------------------------
BEGIN
  -- Exit if Decimals parameter is other than 0 (zero)
  IF DECIMALS <> 0 THEN LEAVE ProcessZeroDecimals; END IF;
  -- Perform if Decimals parameter is equal to 0 (zero)
  Code Goes Here
  Code Goes Here
  Code Goes Here
----------------------------------------------------------------
END;--ProcessZeroDecimals:
----------------------------------------------------------------

----------------------------------------------------------------
ProcessNonZeroDecimals:
----------------------------------------------------------------
BEGIN
  -- Exit if Decimals parameter is equal to 0 (zero)
  IF DECIMALS = 0 THEN LEAVE ProcessNonZeroDecimals; END IF;
  -- Perform if Decimals parameter is other than 0 (zero)
  Code Goes Here
  Code Goes Here
  Code Goes Here
----------------------------------------------------------------
END; --ProcessNonZeroDecimals:
----------------------------------------------------------------

By testing the value of Decimals first we will either decide to LEAVE the code block or perform the code within that code block. The use of LABEL combined with the LEAVE statement makes for a very elegant solution. Here is the complete ElevateDB funtion:

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

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

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

DECLARE i INTEGER;
DECLARE j INTEGER;

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

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

----------------------------------------------------------------
ProcessZeroDecimals:
----------------------------------------------------------------
BEGIN
  -- Exit if Decimals parameter is other than 0 (zero)
  IF DECIMALS <> 0 THEN LEAVE ProcessZeroDecimals; END IF;
  -- Perform if Decimals parameter is equal to 0 (zero)
  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 = Separator + Str2;
    END IF;
  END WHILE;
----------------------------------------------------------------
END;--ProcessZeroDecimals:
----------------------------------------------------------------

----------------------------------------------------------------
ProcessNonZeroDecimals:
----------------------------------------------------------------
BEGIN
  -- Exit if Decimals parameter is equal to 0 (zero)
  IF DECIMALS = 0 THEN LEAVE ProcessNonZeroDecimals; END IF;
  -- Perform if Decimals parameter is other than 0 (zero)
  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 = Separator + 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!

Here is the fFormatThousands function at work against the DBDemos database.

SELECT
 OrderNo
,CustNo
,fFormatThousands(ItemsTotal,2) as "Items Total"
,fFormatThousands(AmountPaid,2) as "Amount Paid"
,fFormatThousands(ItemsTotal - AmountPaid,2) as "Balance Due"
FROM orders
ORDER BY
ItemsTotal - AmountPaid DESC;

 

Semper Fi,
Gunny Mike

end.