10 Replies Latest reply on Sep 28, 2012 11:07 AM by Emmanuel

    ExecuteSQL parameters, format and speed

    Emmanuel

      Title

      ExecuteSQL parameters, format and speed

      Post

           Hi,

           I am now working on Filemaker 12 to deploy a dashboard including charts with an "ExecuteSQL"  solution. I am facing some sql language limitations.

           1) Is it possible to have flexible "SELECT" that is having either case or if function within the query?

                       ex: SELECT Case (radiobutton choice 1; "x"; radiobutton choice 2; "y") FROM (...)

                              OR

                       ex: Case (radiobutton choice 1; SELECT "x"; radiobutton choice 2; SELECT "y") FROM (...)

            

           2) In the same way, can we make the dynamic parameters of the query so that includes some Case or if calculations?

            

           3) How to choose the format of the result? The results are given with 3 decimals when the calculations are based on currency.

            

           4) How can I generate a top list of 10 result from a query when sorted?

            

           5) When SELECT gets 2 fields (let's say customer name and sum of sales) and given a "field seperator" as a tab (Char (9)), how can I make the second result allign on the rigth?

            

           Thank you

        • 1. Re: ExecuteSQL parameters, format and speed
          philmodjunk

               1) You have two options (but see 2) below:

                   a) put the case function outside of the Execute SQL call and have each possible outcome use a different ExecuteSQL function call:

                       Case ( radiobuttonField = 1 ; ExecuteSQL (...
                                  RadiobuttonField = 2 ; ExecuteSQL (...

                   b) or you can keep in mind that the text inside the first parameter is a text expression. You can use quotes and the ampersand to construct a text expression that evaluates to produce different SQL code:

                       ExecuteSQL(" SELECT " & Case ( radiobuttonField = 1; "x" ; radiobuttonField = 2 ; "y" ) & //continue your SQL expression here )

               2) ExcuteSQL allows you to insert values into the SQL expression by using the ? placeholder:

                   ExecuteSQL ( "Select ? " ; Char ( 9 ) ; ¶ ; case ( ....

               I suggest this this option over 1 above as it makes constructing the SQL much easier.

               3) Not sure on this one. You might have to add a calculation field that Rounds the result and then you reference the calculation field instead of the raw value field in your SELECT statment.

               4) Only method I've been able to figure out is to put the Results of the ExecuteSQL function inside a LeftValues function to limit the results to a specified number of values. This assumes that you use ¶ as the record separator and that you use ORDER BY to sort your results.

               Leftvalues ( ExecuteSQL (     ) ; 5 ) would limit the result to first 5 records returned by the query. It also leaves an extra return after the last value that you might have to remove in some situtations.

               5) Put the field on your layout, select it and go the the Appearance section of the Inspector. You'll find a place where you can specifiy the type of tab stop and its location.

               PS. I strongly recommend using SeedCode's free SQL Explorer tool to minimize the frustration of getting a ? each time some small detail in your SQL expression isn't quite right.

               You can also research the Select Statement syntax used by this function if you look up the Select Statement in the ODBC and JDBC guide PDF document provided with each copy of FileMaker 12. You cand find it in the product documentation sub menu in Help.

          • 2. Re: ExecuteSQL parameters, format and speed
            Emmanuel

                  

                 Unfortunately, even though the SQLExplorer is a useful tool, it is too basic for the calculations I am trying to achieve. Nonetheless, I have progressing very well in my building because of your helpful advises. 
                  
                 Concerning Execute SQL, how can I make a dynamic parameter that refers to all of the options? In the following example, I can get the results when it concerns a particular "productline". However, since no product are inside a "all" category, I would like to be able to generate all of the possibilities. I trying "NOT ISEMPTY" and doesn't give me anything. Is there another trick to go through this issue?
                  
                 ExecuteSQL ( " 
                 SELECT SUM (b.Qty)
                 FROM \"Invoices\" a 
                 INNER JOIN \"LineItems\" b ON a.\"__pkInvoiceID\" = b.\"_fkInvoiceID\" 
                 INNER JOIN \"Products\" c ON b.\"_fkItemID\" = c.\"__pkProductID\"
                 WHERE a.cYear = ? AND c.Productline = ?
                 GROUP By a.cMonth 
                 ORDER BY a.cMonth " ; Char ( 9 ) ;  ¶ ; Charts::Year1
            Case (
            Charts::Productline   ≠ "All";
            Charts::Productline;
            Charts::Productline  = "All";
            NOT IsEmpty ( Charts::Productline )
                 )

                  

            • 3. Re: ExecuteSQL parameters, format and speed
              philmodjunk

                   If you used:

              Case (
              Charts::Productline   ≠ "All";
              Charts::Productline;
              Charts::Productline  = "All";
              True
                   )
                   It would produce the same result, returning the number 1 when productline = "all".

                   c.Productline = 1 isn't going to work.

                   Perhaps:

                   WHERE a.cYear = ??
                   GROUP By a.cMonth 
                   ORDER BY a.cMonth " ; Char ( 9 ) ;  ¶ ; Charts::Year1
              Case (
              Charts::Productline   ≠ "All";
              "AND c.Productline = " & Charts::Productline;
              Charts::Productline  = "All";
                   ""
                   )
                   And my preferences would rewrite that case function as:
                   If ( Charts::Productline ≠ "All"; "AND c.Productline = " & Charts::Productline )

                    

              • 4. Re: ExecuteSQL parameters, format and speed
                Emmanuel

                     I tested the different parts of your suggestion seperately and they work, but as soon as I put them in the parameter field, the ? pops up... Just like if the format of the calculation was not supported... :(

                     ExecuteSQL ( " 

                      

                     SELECT SUM (b.Sales)
                     FROM \"Invoices\" a 
                     INNER JOIN \"LineItems\" b ON a.\"__pkInvoiceID\" = b.\"_fkInvoiceID\" 
                     INNER JOIN \"Products\" c ON b.\"_fkItemID\" = c.\"__pkProductID\"
                     WHERE a.cYear = ?  ? 
                     GROUP By a.cMonth 
                     ORDER BY a.cMonth " ; Char ( 9 ) ;  ¶ ;  Charts::Year1 ; If ( productline_choice ≠ "All"; " AND c.Productline = " & productline_choice ))
                      
                • 5. Re: ExecuteSQL parameters, format and speed
                  philmodjunk

                       From the help entry on ExecuteSQL:

                       arguments- one or more expressions that are evaluated and used as values for the dynamic parameters in the query statement.
                        
                       I suspect that my suggestion won't work here and that leads us to an earlier approach discussed earlier:

                            ExecuteSQL ( " 

                            SELECT SUM (b.Sales)
                            FROM \"Invoices\" a 
                            INNER JOIN \"LineItems\" b ON a.\"__pkInvoiceID\" = b.\"_fkInvoiceID\" 
                            INNER JOIN \"Products\" c ON b.\"_fkItemID\" = c.\"__pkProductID\"
                            WHERE a.cYear = ?  " & If ( productline_choice ≠ "All"; " AND c.Productline = " & productline_choice ) &
                            "GROUP By a.cMonth 
                            ORDER BY a.cMonth " ; Char ( 9 ) ;  ¶ ;  Charts::Year1 )

                        

                  • 6. Re: ExecuteSQL parameters, format and speed
                    Emmanuel

                         I hope it is okey to ask you another suggestion since the calculations still dont work :(

                          

                         Thousand thanks

                          

                          
                          
                    • 7. Re: ExecuteSQL parameters, format and speed
                      philmodjunk

                           It should work. I don't think you need to use c.\"productline\" inside the If function, but you can try that and see. "Still doesn't work" doesn't give me much to go on. Getting that dratted ? again or getting an incorrect set of values returned?

                           Can you copy and paste from the specify calculation dialog so that I can see exactly what you have at this point?

                      • 8. Re: ExecuteSQL parameters, format and speed
                        Emmanuel

                             Yep, sorry...

                             If I simply remove the 2nd WHERE condition, it works just rigth. It must be the syntax of it. Even though I understand it when I read it, I don't know want to change to make it work. So here is what I got:

                              

                              

                             Case (
                              
                              
                             Charts::Basechoice = "Units"; 
                              
                             ExecuteSQL ( " 
                             SELECT SUM (b.Qty)
                             FROM \"Invoices\" a 
                             INNER JOIN \"LineItems\" b ON a.\"__pkInvoiceID\" = b.\"_fkInvoiceID\" 
                             INNER JOIN \"Products\" c ON b.\"_fkItemID\" = c.\"__pkProductID\"
                             WHERE a.cYear = ?  " & If ( Productline_choice ≠ "All"; " AND c.\"productline\" = " & productline_choice ) &"
                             GROUP By a.cMonth 
                             ORDER BY a.cMonth " ; Char ( 9 ) ;  ¶ ;  Charts::Year1 );
                              
                              
                              
                             Charts::Basechoice = "Amount"; 
                              
                             ExecuteSQL ( " 
                             SELECT SUM (b.Sales)
                             FROM \"Invoices\" a 
                             INNER JOIN \"LineItems\" b ON a.\"__pkInvoiceID\" = b.\"_fkInvoiceID\" 
                             INNER JOIN \"Products\" c ON b.\"_fkItemID\" = c.\"__pkProductID\"
                             WHERE a.cYear = ?  " & If ( Productline_choice ≠ "All"; " AND c.\"productline\" = " & productline_choice ) &"
                             GROUP By a.cMonth 
                             ORDER BY a.cMonth " ; Char ( 9 ) ;  ¶ ;  Charts::Year1 )
                              
                              
                             )
                              
                              
                             As you can see, I was not able the get the SELECT ? placeholder working neither but just two SQL's are managable :)
                             At worst, I can create two more CASE scenarios to not include the "productline" parameter.
                              
                        • 9. Re: ExecuteSQL parameters, format and speed
                          philmodjunk

                               I think we are missing a space character after the If function such that we are getting the text:

                               And c."productline" = WrenchGroup by....

                               Also, come to think of it, we may need to quote the value returned by productline_choice

                               If ( Productline_choice ≠ "All"; " AND c.\"productline\" = " & Quote ( productline_choice ) ) &
                               " Group By

                          • 10. Re: ExecuteSQL parameters, format and speed
                            Emmanuel

                                 !!! I can't belive it! It is indeed the missing space that was the bug!

                                 I really appreciate your support!

                                  

                                 Again, thank you!