6 Replies Latest reply on Mar 20, 2013 9:17 AM by greglane

    SQL Select formatting Number as decimal

    GiancarloMeak

      I have the following SQL

       

      ExecuteSQL (

      "SELECT orgName01, field02

      FROM table01 WHERE checkBox01 = 1 and id = ?"

      ;

      "" ; ""; table01::id)

       

      I'd like to get the number of field02 formatted with a fixed number of decimals of two, but SQL retrieve the raw number unformatted

      This could be the result:

      Apekta inc. 123,00

      Acme inc. 128,10

      ....

       

      Thanks for helping

      Gianco

        • 1. Re: SQL Select formatting Number as decimal
          wimdecorte

          You can put a Round(  <sql execute> ; 2 ) around the whole thing

          • 2. Re: SQL Select formatting Number as decimal
            greglane

            It's a little bit convoluted, but you could use something like:

             

            ExecuteSQL (

            "SELECT  orgName01, STRVAL(INT(field02)) || LEFT(STRVAL(field02 + .001 - INT(field02)), 3)

             

            FROM table01  WHERE checkBox01 = 1 and id = ?"

            ;

            "" ; ""; table01::id)

             

             

            BTW, if you might have negative values, use something like this instead:

             

            ExecuteSQL (

            "SELECT  orgName01, STRVAL(INT(field02)) || LEFT(STRVAL(ABS(field02) + .001 - INT(ABS(field02))), 3)

             

            FROM table01  WHERE checkBox01 = 1 and id = ?"

            ;

            "" ; ""; table01::id)

             

            Message was edited by: Greg Lane to include alternate solution for negative values

            • 3. Re: SQL Select formatting Number as decimal
              DavidJondreau

              Wim, you should know better!

               

              Round() won't give decimals if they end in zero. 2.10 will be 2.1

               

              Try a custom function by searching for decimal at www.briandunning.com

              1 of 1 people found this helpful
              • 4. Re: SQL Select formatting Number as decimal
                wimdecorte

                DavidJondreau wrote:

                 

                Wim, you should know better!

                 

                 

                 

                Good catch!

                • 5. Re: SQL Select formatting Number as decimal
                  GiancarloMeak

                  Thanks a lot,

                   

                  it works :-).

                   

                  There is a way to have the "," and not the "." as decimal separator?

                   

                  I have to pick up a more advanced book on SQL, any suggestion?

                   

                  Gianco

                  • 6. Re: SQL Select formatting Number as decimal
                    greglane

                    To use a comma as the decimal separator, try something like this:

                     

                    STRVAL(INT(field02)) || ',' || SUBSTR(STRVAL(ABS(field02) + .001 - INT(ABS(field02))), 2, 2)

                     

                    The best resource for a description of the functions available to use within the ExecuteSQL function is Chapter 7 of FileMaker's ODBC and JDBC Guide. You can open it from FileMaker Pro's Help menu: Help->Product Documentation->ODBC and JDBC Guide. The lists of string, number, and date functions are on pages 51-53.

                     

                    The chapter is well-written with lots of great examples. However, there are some things that aren't relevant to the ExecuteSQL function:

                     

                    • All statements that modify data or schema. Only the SELECT statement works with the ExecuteSQL function.
                    • Functions and operators related to container fields or binary data.
                    • The brace ({}) syntax for dates and times.

                     

                    Also, there are some very useful logical functions that are missing from the document, which seem to work anywhere SQL can be used with FileMaker:

                     

                    • COALESCE
                    • CASE WHEN
                    • NULLIF

                     

                    Greg

                    1 of 1 people found this helpful