Trying to add some text to a query.

 

For example here's a real simple calculation with a query that returns some text

ExecuteSQL(
"SELECT n_transactionAmountGross,  t_transactionModel
FROM Transactions  
WHERE  FamilyID_CustomerID = ?"   ;    " : "   ;   ""     ;   FamilyID )
)

 

For a particular record it could return something like

 

100 : Membership 

200 : Lesson

 

I'd like to show the amount as currency. 

 

Even though the amount is a number in Filemaker, I assume that since the calculation is returning text the amounts are now a text string.  So i'd like to prepend a dollar sign. But any number of operations like   "$" +     or   Quote like used in the following don't work. I've tried it as a parameter using a variable as well.

ExecuteSQL ( 
"SELECT " & Quote("$") & "n_transactionAmountGross,  t_transactionModel
FROM Transactions  
WHERE  FamilyID_CustomerID = ?"   ;    " : "   ;   ""     ;   FamilyID )
)

 

Anyone have a solution for inserting text into a query?

 

---------

BTW: Even if I was only returning the amount, and switched the calculation to return a number I cannot CAST the number as smallmoney which might be a solution.  I guess ultimately whatever data the calculation returns will override the datatype returned by SQL.

There are different syntaxes; I found that the one from Oracle works in FM (see screenshot). Put the string in single quotes.

ExecuteSQL
( "
  SELECT '$' || n_transactionAmountGross,  t_transactionModel
  FROM Transactions  
  WHERE  FamilyID_CustomerID = ?

  "   ;    " : "   ;   ""     ;   FamilyID
)