Scenario:
When the tax total value is less than 1, the custom field designed to store the tax total in words displays an invalid expression.
Solution:
CASE
WHEN {taxtotal} = 0 THEN ‘ZERO ONLY’
WHEN {taxtotal} < 1 THEN
(CASE
WHEN ROUND({taxtotal} * 100, 0) > 0 THEN
TO_CHAR(TO_DATE(ROUND({taxtotal} * 100, 0), ‘J’), ‘JSP’) || ‘ PAISE ONLY’
ELSE ”
END)
ELSE
(CASE
WHEN FLOOR({taxtotal}/100000) > 0 THEN
TO_CHAR(TO_DATE(FLOOR({taxtotal}/100000), ‘J’), ‘JSP’) || ‘ LAKH ‘
ELSE ”
END) ||
(CASE
WHEN FLOOR(MOD({taxtotal}, 100000) / 1000) > 0 THEN
TO_CHAR(TO_DATE(FLOOR(MOD({taxtotal}, 100000) / 1000), ‘J’), ‘JSP’) || ‘ THOUSAND ‘
ELSE ”
END) ||
(CASE
WHEN MOD({taxtotal}, 1000) > 0 THEN
TO_CHAR(TO_DATE(FLOOR(MOD({taxtotal}, 1000)), ‘J’), ‘JSP’) || ‘ ‘
ELSE ”
END) ||
(CASE
WHEN ROUND(MOD({taxtotal}, 1) * 100, 0) > 0 THEN
‘AND ‘ ||
TO_CHAR(TO_DATE(ROUND(MOD({taxtotal}, 1) * 100, 0), ‘J’), ‘JSP’) || ‘ PAISE ‘
ELSE ”
END) ||
‘ONLY’
END