Jump to content

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


This topic is 3132 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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 by Justin Close
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

  • Like 1
Link to comment
Share on other sites

  • 2 weeks later...

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

Link to comment
Share on other sites

This topic is 3132 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.