Get ESQL to return always 2 decimal places (currency)?

[Justin Close]

I'm having a bit of an issue with an ESQL query.  I would like it to always return 2 decimal places on the value retrieved.  This is a currency field, and "$45.5" looks weird.  I am generating a list of payments to displayed (via a global variable in the UI) to the user, so it should look money-like.  It's a simple 'here's what you currently have set up' kind of display.

So here's the basic query (I believe folks here are familiar with the GFN() and GTN() functions - they are just a way to robustify the query in FileMaker):

ExecuteSQL (
    "SELECT '$' || " & GFN ( Payments::Amount ) &
    " FROM " & GTN ( Payments::aaPaymentUUID ) &
    " WHERE " & GFN ( Payments::aLineItemID_fk ) &
 " = ? "; "" ; "" ; $ID )

 

I have also tried various versions of this without luck (using "numeric(10,2)", or "decimal(10,2)"):

ExecuteSQL (
    "SELECT '$' || CAST ( " & GFN ( Payments::Amount ) & " as decimal (5,2) )" &
    " FROM " & GTN ( Payments::aaPaymentUUID ) &
    " WHERE " & GFN ( Payments::aLineItemID_fk ) & " = ? "
; "" ; "" ; $ID )

But I always get responses like (yes, they should be the same - it's an equal payment calculator, so the last one might be different):
 

$125.5
$125.5
$125.5

When I would like it to be:

$125.50
$125.50
$125.50

Anyone have a quick answer as to how to get each record value to have 2 decimal places after it?

I have shied away from doing in FileMaker string manipulation because I thought the ESQL fix would be easy; and it would save some steps in the script.  But maybe it's easier to do it in FM and just reprocess the whole list.

Edited August 17, 2015 by Justin Close

[beverly]

from Greg Lane (on another forum), I can confirm that this works as stated:

SELECT '$' || COALESCE(CAST(INT(numField) AS VARCHAR),'0') || '.' || SUBSTR(CAST(numField - INT(numField) AS VARCHAR) || '000',2,2)

FROM myTable

the "||" is a way to concatenate text output into one column. It may be beneficial to create a custom function to insert this long string (in the SELECT) into your queries. Just make the 'numfield' the parameter. I also may prepend the '$' or not.

beverly

[otmar]

Any disadvantages/advantages to use STRVAL() instead of CAST() here? Would shorten the expression a bit.

otmar 

[beverly]

Any disadvantages/advantages to use STRVAL() instead of CAST() here? Would shorten the expression a bit.

otmar 

re-write the query using STRVAL(), run some tests & report back. what I know, I know because I test.

beverly

[otmar]

You're absolutely right. Performance wise there's no difference CAST as VARCHAR and STRVAL seem to do the same, but the SQL-way is 4 times as fast as post processing with a recursive CF.

otmar

[beverly]

for those who wondered, this is how I revised the CAST() to use STRVAL(). NOTE the placement, as it's important:

(yes, I broke the string out to make it easier to understand the 'parts' & you would supply the field not the static value for _num)

 

Let ( 

[ _pre = " '$' " // change as needed

; _num = round( 2345.017; 2) // change to actual field ref & round first

; _query = " SELECT "

& if ( not isempty(_pre); _pre & "||" )

& " STRVAL( COALESCE( INT( " & _num & "  ) , 0 ) ) "

& " || '.' "

& " || SUBSTR( STRVAL( " & _num & " - INT( " & _num & " ) ) || '000', 2, 2 ) "

& " FROM test "

; $result = ExecuteSQL ( _query ; "" ; "" )

]; $result

) // $2345.02