-
-
Notifications
You must be signed in to change notification settings - Fork 653
Qt_script_frmamt
Paweł Salawa edited this page Jul 17, 2019
·
2 revisions
| Language: | Qt Script |
|---|---|
| Plugin for language: | ScriptingQt |
| How to use: | Create custom SQL function, set it to "scalar" type. Suggested name: fmtAmt |
| Function arguments | Keep undefined |
| Function usage: | SELECT fmtAmt(colName) from tableName |
| Description: | Formats numbers like "123456789" into "123.456.789,00" or "23434.68" to "23.434,68". When using frmAmt() it is useful to change the character font for database results to a fixed width font such as "Lucida Sans Typewriter" or similar. |
| Author: | JrgMyr |
The code:
var t = arguments[0]; var lg = 17; if (t > -9E11 && t < 1E12) { if (t >= 0.0) t += 0.005; else t -= 0.004; t = ' ' + t; var n = t.indexOf('.'); if (n == -1) t = t + ',00' else { if (n == t.length - 1) t = t + '0'; t = t.substr(0, n) + ',' + t.substr(n+1, 2); } t = t.substr(-lg); if (t.substr(lg-7, 1) >= '0') t = t.substr(1, lg-7) + '.' + t.substr(lg-6, 6); if (t.substr(lg-11, 1) >= '0') t = t.substr(1, lg-11) + '.' + t.substr(lg-10, 10); if (t.substr(lg-15, 1) >= '0') t = t.substr(1, lg-15) + '.' + t.substr(lg-14, 14); } return t;
Example usage:
SELECT Company, Year, frmInt(COUNT(*)) AS LineItems, frmAmt(SUM(amount)) AS TotalAmount FROM datatable GROUP BY Company, Year;