7 Replies Latest reply on Jul 6, 2016 6:11 AM by Stu412

    Select full decimals using ExecuteSQL

    Stu412

      Hi all

       

      I'm trying to select from the following data set on a field called FeeValue.  I then pass this into a custom function to calculate the upper quartile - FileMaker Custom Function:ThirdQuartile ( valueList ) :  The data in my raw fields looks like this:

       

      0.52

      0.56

      0.94

      0.44

       

      This uses the following ExecuteSQL:

       

      ExecuteSQL ("

       

      Select (FeeValue)from Fees where

       

      Area = ? and

      Category = ?  and

      Description = ? and

      Service = ? and

      cYearEndedIn = ?

       

      Order by FeeValue ASC

      ";"";"";

       

      "North";

      "Generic Category";

      "Generic Description";

      "Named Service";

      2015

       

      )

       

      I noticed something was wrong when the result I was given was 75.00.  The result here should be 0.75.  (94+56/2)

       

      I checked further and put the ExecuteSQL into a small script against a global variable to check what was actually being collected and the select gives me the following via the data checker:

       

      .52

      .56

      .94

      .44

       

      This is despite the source data having a leading zero.

       

      Two questions arise:

       

      1) Does ExecuteSQL strip the leading zero, and if so, would having it there make any difference?

      2) Has anybody else used the CF I'm working with and found there are similar results with numbers under 1.00?

       

      Thanks

        • 1. Re: Select full decimals using ExecuteSQL
          beverly

          Stu, answers "inline":

           

          1) Does ExecuteSQL strip the leading zero, and if so, would having it there make any difference?

           

          the ExecuteSQL() function returns TEXT. You will not see leading 0's in the "decimal numbers".

           

          2) Has anybody else used the CF I'm working with and found there are similar results with numbers under 1.00?

           

          there is a link to the author of the CF on the page you referenced. have you tried to contact Daniele? and/or post a comment on the page

          beverly

          • 2. Re: Select full decimals using ExecuteSQL
            Magnus Fransson

            Hi Stu412,

             

            The custom function is made using "word" functions. (RightWords, WordCount, MiddleWords and so on.) And I think that "." is a "Word delimiter" and therefore disappear.

            The custom function should be remade using "Values" functions instead. (RightValue, ValueCount, MiddleValue and so on.)

             

            But that is not all. The CF is also using the Truncate function, which would remove all decimals and in your case return zero.

             

            With best regards Magnus Fransson.

            • 3. Re: Select full decimals using ExecuteSQL
              Stu412

              Beverly

               

              One thing I've managed to do here, based on something you showed me previously, is to add something to the ExecuteSQL just after the 'select':

               

              ExecuteSQL ("

               

              Select ''||(FeeValue)from Fees where

               

              Area = ? and

              Category = ?  and

              Description = ? and

              Service = ? and

              cYearEndedIn = ?

               

              Order by FeeValue ASC

              ";"";"";

               

              "North";

              "Generic Category";

              "Generic Description";

              "Named Service";

              2015

               

              )

               

              I'm using SQL for other numeric work throughout my project (somehow!) and it seems to be working with this setup as above.  This will only work when complimented with 'proper' data, ie, instead of .74 showing in a field, it needs to be 0.74 etc.

               

              Previously I've used CAST varchar on MSSQL - I can't see there's anything equivalent to actually change data types??

               

              Thanks

              • 4. Re: Select full decimals using ExecuteSQL
                beverly

                yes! You are concatenating a NULL constant with your field value. The results are text regardless. But it's a handy trick!

                 

                There is a CAST() that can be used with ExecuteSQL() too, but you have to be very careful using it.

                 

                beverly

                • 5. Re: Select full decimals using ExecuteSQL
                  user19752

                  Truncate() is used for index (position of number), not number value. It can be replaced with Int().

                   

                  Let ([

                  values =  RightValues ( valueList; Int ( ( ( ValueCount ( valueList ) - 1) / 2 ) + 1 ) );

                  count = ValueCount ( values ) ;

                  odd =  GetValue ( values ; Int ( count / 2 ) +1 );

                  even = GetValue ( values ; Int ( count / 2 ) )

                  ];

                  Case(

                  Mod ( count ; 2 ) ; odd ; ( even + odd ) / 2

                  )

                  )

                  • 6. Re: Select full decimals using ExecuteSQL
                    Magnus Fransson

                    Hi  user19752,

                     

                    You are right. After my previous post I took a closer look at the CF. And the Truncate() function is used for "positions" only. They can either remain or be replaced with either Int() or Floor().

                     

                    My claim that the dot (".") should be a "word delimiter" is actually more complex then just that. It seems as, when next to another delimiter (like "new line" for example) it is removed. But when between two numbers it is considered to be a part of that word. That is why Stu can make the CF work by concatenating zeros ("0") infront of the values.

                     

                    But it would be more "right" and reliable to use "Value" functions, like in your example. (Maybe even in conjunction with "GetAsNumber()" function.) That would make the concatenation with zeros redundant and unnecessary.

                     

                    With best regards Magnus Fransson.

                    • 7. Re: Select full decimals using ExecuteSQL
                      Stu412

                      Fran55on, User19752, Beverly

                       

                      Thanks for the help with this one.  I was trying to redo the CF yesterday to set as values, not words.  The inclusion of the INT command here to replace Truncate has managed to fix a couple of issues:

                       

                      1) I can use my original FeeValue field, even though the leading zeros may be stripped.

                      2) I concatenate a null character at the start of the ExectuteSQL to capture the values as I need them

                      3) The new 'numeric' CF works perfectly and is now returning the values I expect (ie, matching those I calculate away from FM using traditional methods!)

                       

                      Thanks everyone for help from you all!