14 Replies Latest reply on Jan 15, 2016 8:09 AM by alecgregory

    SQL results include commas

    Stu412

      Hi all,

       

      Probably a simple one this, but I didn't expect it.  I'm testing some SQL code below, trying out the CASE statement as per FM advanced training guide.

       

      ExecuteSQL ( "

       

      SELECT Sum (CASE RowID WHEN 1 THEN \"Amount\" END) ,

             Sum (CASE RowID WHEN 2 THEN \"Amount\" END) ,

             Sum (CASE RowID WHEN 3 THEN \"Amount\" END)

       

      FROM TestSQL_Static

      WHERE RowID = ?";

       

      "" ; "" ;

       

      RowID

       

      )

       

      My results on a list report sorted by a sub summary part set on RowID for my own clarity shows a two commas at the right side of the first row's result, one either side of the second row's result and two before the third row's result.  I suspect it's something to do with the commas on the Sum statements, but removing them breaks the query.  Screenshot attached.

       

      Any help gratefully appreciated.

       

      Thanks

        • 1. Re: SQL results include commas
          nicolai

          It would be easier if you posted what are you trying to do as well.

           

          The commas are not from Case statement, you are actually getting what you requested for

          • 2. Re: SQL results include commas
            Stu412

            Hi Nicolai

             

            I'm literally just looking for it to return the values as these are the sole values per each record.  As I mentioned, just testing at the moment.  I don't understand where the commas are coming from though as they're not required.

             

            Thanks

            • 3. Re: SQL results include commas
              nicolai

              You query is :  give me one row only with three values separated by commas, the values are calculated based on value in RowID

               

              If there is no value, you will get an empty string.

               

              Your first row for RowID = 1 gives you  ->  Sum of Amount comma empty string comma empty string

              second row for RowID =  2 gives you  ->  empty string comma Sum of Amount comma empty string

              third row for RowID =    3 gives you  ->  empty string comma comma empty string Sum of Amount

               

              and the following rows do not have any values as RowID is bigger then 3. Remember you are getting 3 values back and not one string.

              • 4. Re: SQL results include commas
                alecgregory

                The commas are the default column separators that filemaker uses when returning results from ExecuteSQL (you can override it by putting another character or calculation as the second parameter of the ExecuteSQL function). You could get rid of the commas by concatenating the results:

                 

                ExecuteSQL ( "

                 

                  SELECT Sum (CASE RowID WHEN 1 THEN \"Amount\" END)  ||

                       Sum (CASE RowID WHEN 2 THEN \"Amount\" END) ||

                       Sum (CASE RowID WHEN 3 THEN \"Amount\" END)

                 

                FROM TestSQL_Static

                WHERE RowID = ?";

                 

                "" ; "" ;

                 

                RowID

                 

                )

                 

                However, unless I've misunderstood your intention, I think you could do without the Case statement altogether:

                 

                ExecuteSQL ( "

                 

                  SELECT

                    Sum ( \"Amount\" )

                  FROM

                    TestSQL_Static

                  WHERE

                    RowID = ?";

                  "";

                  "";

                  RowID

                )

                 

                Obviously that doesn't help you test the case statement. To test that properly you would need to think of a scenario where it was required to get a certain result.

                • 5. Re: SQL results include commas
                  nicolai

                  Try this:

                   

                  ExecuteSQL ( "

                      SELECT \"Amount\"

                      FROM TestSQL_Static

                      WHERE RowID = ?";

                  "" ; "" ;

                  RowID)

                   

                  If you remove WHERE clause you will get the list of all Amounts from the table

                  • 6. Re: SQL results include commas
                    Stu412

                    Thanks for all the help here.

                     

                    The final aim is using this to produce a virtual list for cross tab reporting.  Now I have the principal in place, the CASE statement is used to determine a period/quarter in time of a whole bunch of records I have and then parse these out using GetValue.

                    • 7. Re: SQL results include commas
                      beverly

                      Alec is correct, comma is the default "field/column separator".

                       

                      However, the CASE can be worked thusly and perhaps give you the desired:

                       

                      SUM(

                       

                           CASE RowID

                                WHEN 1 THEN \"Amount\"

                                WHEN 2 THEN \"Amount\"

                                WHEN 3 THEN \"Amount\"

                                ELSE ""

                                END

                           )

                       

                      It is how CASE works rather like FM's Case() function as we don't have a SQL CHOOSE() function available to the ExecuteSQL(). The ELSE is optional, of course.

                       

                      beverly

                      • 8. Re: SQL results include commas
                        alecgregory

                        I think that would definitely work. But please indulge me a moment if you have time: couldn't it work with just a where clause as I described above or have I missed something?

                        • 9. Re: SQL results include commas
                          beverly

                          Alec, I believe the WHERE would need an IN (1,2,3) to work for various number of matches. As I saw the png from Stu412, not every Record would have a "match". But I may have misunderstood.


                          WHERE RecID IN (1,2,3)

                           

                          Your use of the concatenation was a clever way to avoid the "," columns separator.

                           

                          However, I wanted to point out the usage of the SQL CASE function as it was designed, if that was Stu's intent.

                           

                          Nicolai's answer was marked as Correct, so perhaps it was the intent, but the default values for ExecuteSQL() were unknown.

                           

                          beverly

                          • 10. Re: SQL results include commas
                            nicolai

                            alecgregory


                            No, you answer is correct as well, but I think Beverly's answer is closer as it demonstrates proper usage of Case clause, which I think that Stu412 wanted

                            • 11. Re: SQL results include commas
                              Stu412

                              Beverly is right in that not every record may return a value, so for example, Case=1 may have something, but Case=4 can very easily be empty in my particular case.  The use of the case statement in this way helps cover these eventualities.

                               

                              I've got plenty more to look at but I'm so much further on that I was 30 minutes ago!  Thanks for everybody's help.

                              • 12. Re: SQL results include commas
                                beverly

                                OK, I see the p. 559 in the FTS Advanced guide. it did have the four quarters and did need to have the 'blanks'.

                                 

                                BTW, the "," or "" (default) can be changed to ";", if that helps in any way. Or even as returns between the values (up to four values per field result) and then you have a way to use the value list functions to get at each one.

                                 

                                ExecuteSQL ( query

                                ; Char(13) ; Char(13)

                                )

                                 

                                beverly

                                • 13. Re: SQL results include commas
                                  nicolai

                                  Beverly Voth wrote:


                                  Nicolai's answer was marked as Correct, so perhaps it was the intent

                                   

                                  Totally undeserved, I say, but I did try to explain where the commas were coming from, which, to be fair, was part of the original question.

                                   

                                  A bit off topic, Stu412, but I do not think FileMaker is the best app to learn SQL (apologies to the gods of FM, I will bring a sacrifice later). I would recommend starting with a generic syntax and using something like SQL Fiddle

                                   

                                  Good luck

                                  • 14. Re: SQL results include commas
                                    alecgregory

                                    Thanks guys. I see what you mean about only wanting ids between 1 and 3. I would take the opportunity to use the BETWEEN clause in that case, it doesn't get much love!