It's not really comparable (in the mathematical sense of the word), but if we're talking "ugliest thing I ever created", this is mine.
As for the reasons, this is for a BI report on performance measure achievement. Some performance measures are percentages, some are numbers, some are dollar amounts. Since they're all mixed together we couldn't use the report's built-in column formatting. Also, we needed the report to be able to do aggregation of totals, so the values passed to the report had to be numeric - that meant we couldn't do any preliminary stuff in the logical layer, such as converting to a string and locating the decimal point; it all had to be in the report formula. Further, the only number-to-string conversion function available was CAST(value AS CHARACTER(max length)) - no formatting options could be given. Experimentation showed that CAST AS CHARACTER sometimes returned two decimal places, sometimes one, and sometimes none.
The report wound up as a union of three queries, one for each of the data types. Formatting the numbers and the percentages was pretty straightforward. This is what we wound up with for the dollar values. And no, we couldn't split it onto multiple lines for what little clarity might have been possible.
CONCAT(CASE WHEN "Fact - Sales Actual Empower".Actual < 0 THEN '-' ELSE '' END, CONCAT('$', CASE WHEN LENGTH(CASE WHEN LOCATE('.', CAST(ABS("Fact - Sales Actual Empower".Actual) AS CHARACTER (50))) = 0 THEN CAST(ABS("Fact - Sales Actual Empower".Actual) AS CHARACTER (50)) ELSE SUBSTRING(CAST(ABS("Fact - Sales Actual Empower".Actual) AS CHARACTER (50)), 1, LOCATE('.', CAST(ABS("Fact - Sales Actual Empower".Actual) AS CHARACTER (50))) -1) END) > 9 THEN INSERT(INSERT(INSERT(CASE WHEN LOCATE('.', CAST(ABS("Fact - Sales Actual Empower".Actual) AS CHARACTER (50))) = 0 THEN CAST(ABS("Fact - Sales Actual Empower".Actual) AS CHARACTER (50)) ELSE SUBSTRING(CAST(ABS("Fact - Sales Actual Empower".Actual) AS CHARACTER (50)), 1, LOCATE('.', CAST(ABS("Fact - Sales Actual Empower".Actual) AS CHARACTER (50))) -1) END, LENGTH(CASE WHEN LOCATE('.', CAST(ABS("Fact - Sales Actual Empower".Actual) AS CHARACTER (50))) = 0 THEN CAST(ABS("Fact - Sales Actual Empower".Actual) AS CHARACTER (50)) ELSE SUBSTRING(CAST(ABS("Fact - Sales Actual Empower".Actual) AS CHARACTER (50)), 1, LOCATE('.', CAST(ABS("Fact - Sales Actual Empower".Actual) AS CHARACTER (50))) -1) END) - 2, 0, ','), LENGTH(CASE WHEN LOCATE('.', CAST(ABS("Fact - Sales Actual Empower".Actual) AS CHARACTER (50))) = 0 THEN CAST(ABS("Fact - Sales Actual Empower".Actual) AS CHARACTER (50)) ELSE SUBSTRING(CAST(ABS("Fact - Sales Actual Empower".Actual) AS CHARACTER (50)), 1, LOCATE('.', CAST(ABS("Fact - Sales Actual Empower".Actual) AS CHARACTER (50))) -1) END) - 5, 0, ','), LENGTH(CASE WHEN LOCATE('.', CAST(ABS("Fact - Sales Actual Empower".Actual) AS CHARACTER (50))) = 0 THEN CAST(ABS("Fact - Sales Actual Empower".Actual) AS CHARACTER (50)) ELSE SUBSTRING(CAST(ABS("Fact - Sales Actual Empower".Actual) AS CHARACTER (50)), 1, LOCATE('.', CAST(ABS("Fact - Sales Actual Empower".Actual) AS CHARACTER (50))) -1) END) - 8, 0, ',') ELSE CASE WHEN LENGTH(CASE WHEN LOCATE('.', CAST(ABS("Fact - Sales Actual Empower".Actual) AS CHARACTER (50))) = 0 THEN CAST(ABS("Fact - Sales Actual Empower".Actual) AS CHARACTER (50)) ELSE SUBSTRING(CAST(ABS("Fact - Sales Actual Empower".Actual) AS CHARACTER (50)), 1, LOCATE('.', CAST(ABS("Fact - Sales Actual Empower".Actual) AS CHARACTER (50))) -1) END) > 6 THEN INSERT(INSERT(CASE WHEN LOCATE('.', CAST(ABS("Fact - Sales Actual Empower".Actual) AS CHARACTER (50))) = 0 THEN CAST(ABS("Fact - Sales Actual Empower".Actual) AS CHARACTER (50)) ELSE SUBSTRING(CAST(ABS("Fact - Sales Actual Empower".Actual) AS CHARACTER (50)), 1, LOCATE('.', CAST(ABS("Fact - Sales Actual Empower".Actual) AS CHARACTER (50))) -1) END, LENGTH(CASE WHEN LOCATE('.', CAST(ABS("Fact - Sales Actual Empower".Actual) AS CHARACTER (50))) = 0 THEN CAST(ABS("Fact - Sales Actual Empower".Actual) AS CHARACTER (50)) ELSE SUBSTRING(CAST(ABS("Fact - Sales Actual Empower".Actual) AS CHARACTER (50)), 1, LOCATE('.', CAST(ABS("Fact - Sales Actual Empower".Actual) AS CHARACTER (50))) -1) END) - 2, 0, ','), LENGTH(CASE WHEN LOCATE('.', CAST(ABS("Fact - Sales Actual Empower".Actual) AS CHARACTER (50))) = 0 THEN CAST(ABS("Fact - Sales Actual Empower".Actual) AS CHARACTER (50)) ELSE SUBSTRING(CAST(ABS("Fact - Sales Actual Empower".Actual) AS CHARACTER (50)), 1, LOCATE('.', CAST(ABS("Fact - Sales Actual Empower".Actual) AS CHARACTER (50))) -1) END) - 5, 0, ',') ELSE CASE WHEN LENGTH(CASE WHEN LOCATE('.', CAST(ABS("Fact - Sales Actual Empower".Actual) AS CHARACTER (50))) = 0 THEN CAST(ABS("Fact - Sales Actual Empower".Actual) AS CHARACTER (50)) ELSE SUBSTRING(CAST(ABS("Fact - Sales Actual Empower".Actual) AS CHARACTER (50)), 1, LOCATE('.', CAST(ABS("Fact - Sales Actual Empower".Actual) AS CHARACTER (50))) -1) END) > 3 THEN INSERT(CASE WHEN LOCATE('.', CAST(ABS("Fact - Sales Actual Empower".Actual) AS CHARACTER (50))) = 0 THEN CAST(ABS("Fact - Sales Actual Empower".Actual) AS CHARACTER (50)) ELSE SUBSTRING(CAST(ABS("Fact - Sales Actual Empower".Actual) AS CHARACTER (50)), 1, LOCATE('.', CAST(ABS("Fact - Sales Actual Empower".Actual) AS CHARACTER (50))) -1) END, LENGTH(CASE WHEN LOCATE('.', CAST(ABS("Fact - Sales Actual Empower".Actual) AS CHARACTER (50))) = 0 THEN CAST(ABS("Fact - Sales Actual Empower".Actual) AS CHARACTER (50)) ELSE SUBSTRING(CAST(ABS("Fact - Sales Actual Empower".Actual) AS CHARACTER (50)), 1, LOCATE('.', CAST(ABS("Fact - Sales Actual Empower".Actual) AS CHARACTER (50))) -1) END) - 2, 0, ',') ELSE CASE WHEN LOCATE('.', CAST(ABS("Fact - Sales Actual Empower".Actual) AS CHARACTER (50))) = 0 THEN CAST(ABS("Fact - Sales Actual Empower".Actual) AS CHARACTER (50)) ELSE SUBSTRING(CAST(ABS("Fact - Sales Actual Empower".Actual) AS CHARACTER (50)), 1, LOCATE('.', CAST(ABS("Fact - Sales Actual Empower".Actual) AS CHARACTER (50))) -1) END END END END))
There was a fair bit of cut and paste involved with creating this thing (once we got the basic component down that says "convert to character, check whether there's a decimal point, and if so strip off everything after it" we used that sucker everywhere; from memory I wound up cutting and pasting higher order components as well), and after creating it we resolved never, ever to attempt to maintain it. So far we haven't had to.
Oh, how I wished for "to_char(num, '999,999,999,990')"... (was going to put that in the tags but the commas are pretty much essential, since inserting them what most of the statement is there for)