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; --MyFistLabelThe 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; --MyFistLabelSo 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.
Still terrible to read when left aligned ;)
ReplyDelete