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.
