1 2 Previous Next 26 Replies Latest reply on Feb 26, 2015 4:06 PM by beverly

    Need Help with Execute Sql Statement

    Lemmtech

      I have a customer table and an orders table and I need to do a few things.

       

      1] Total just the last 2 years worth of sales orders for a customer

       

      2] Show the last date a quote was entered into the orders table. There is field called "Type" and it's either a sale or a quote

       

      I hope I have made myself clear.

       

      Thanks for you help,

       

      Mark L

      LemmtechEnterprises

      markl@lemmtech.com

        • 1. Re: Need Help with Execute Sql Statement
          danielfarnan

          1.

          Let (

          [

          _sql = "SELECT SUM(SalesOrderTotal) FROM SalesTable WHERE SalesOrderDate >= ?" ;

          _cd = GetAsText ( Get ( CurrentDate ) ) ;

          _mth = Month ( GetAsDate ( _cd ) ) ;

          _day = Day ( GetAsDate ( _cd ) ) ;

          _yr = Year ( GetAsDate ( _cd ) ) - 2

          ] ;

           

          ExecuteSQL ( _sql ; "" ; "" ; Date ( _mth ; _day ; _yr ) )

          )

           

           

           

          2.

          SELECT MAX(OrderDate) FROM OrderTable WHERE Type = 'Quote'

          • 2. Re: Need Help with Execute Sql Statement
            erolst

            danielfarnan wrote:

            cd = GetAsText ( Get ( CurrentDate ) ) ;

            _mth = Month ( GetAsDate ( _cd ) ) ;

            _day = Day ( GetAsDate ( _cd ) ) ;

            _yr = Year ( GetAsDate ( _cd ) ) - 2

             

            Why this unnecessary casting back and fro?

            • 3. Re: Need Help with Execute Sql Statement
              nicolai

              The first query does not look right. Try this (modify to match your setup):

               

              ExecuteSQL (

              "SELECT SUM(SalesOrderTotal) FROM SalesTable

              WHERE SalesOrderDate BETWEEN ? AND ?

              AND Type = \"Sale\"

              AND CustomerID =?

              " ;

              "" ; "";  Get ( CurrentDate ), Date ( month (Get ( CurrentDate )); day(Get ( CurrentDate )) ; year(Get ( CurrentDate )) -2); $CustomerID

              )

               

              I assume SalesOrderDate is stored as date. Sorry for syntax errors if any, I wrote this in a text editor and did not test in FM. You need to pass Customer ID as a last parameter.

              • 4. Re: Need Help with Execute Sql Statement
                erolst

                nicolai wrote:

                The first query does not look right.

                Except for the unnecessary casting and the fact that the query should include the customer foreign key, that query should work fine. And where does the $var in your query come from? Also, BETWEEN has been reported to be quite slow – and it's not necessary anyway.

                 

                Let ( [

                  ~cd = Get ( CurrentDate ) ;

                  twoYearsAgo = Date ( Month ( ~cd ) ; Day ( ~cd ) ; Year ( ~cd ) - 2 )

                  ] ;

                  ExecuteSQL ( "

                    SELECT SUM ( SalesOrderTotal )

                    FROM SalesTable

                    WHERE

                      SalesOrderDate >= ? AND

                      Type = ? AND

                      id_customer = ?

                    " ; "" ; "";  twoYearsAgo ; "Sales" ; Customers::id

                  )

                )

                • 5. Re: Need Help with Execute Sql Statement
                  nicolai

                  Variable would have to be set before running the calculation. This will give you an advantage of not being tied up to Customer layout. Anyway, my point was exactly like yours - filtering by customer is missing.

                   

                  BETWEEN has been reported to be quite slow – and it's not necessary anyway.

                   

                  In general -  point taken, as I suppose there will be no sales with a future date. > or < comparison is slower then direct matching anyway, I suspect BETWEEN is just different syntax for this. On the other hand BETWEEN could be modified to give you last year sales (I know, it is not in the original question).

                   

                  Except for the unnecessary casting and the fact that the query should include the customer foreign key

                  That's qualified for me as "not quite right". I did not say "completely wrong". I also changed syntax, but I did not say it is wrong.  Our queries will produce exactly the same result.

                  • 6. Re: Need Help with Execute Sql Statement
                    erolst

                    nicolai wrote:

                    Variable would have to be set before running the calculation. This will give you an advantage of not being tied up to Customer layout.

                     

                    We've no idea of the context this is going to be used in (and if there is a script); maybe it is supposed to be tied to a Customer context, or in some Dashboard thingy … I try to get into the habit of not answering unasked questions.

                     

                    nicolai wrote:

                    Our queries will produce exactly the same result.

                     

                    Right – but mine is prettier …

                    • 7. Re: Need Help with Execute Sql Statement
                      nicolai

                      I agree with both.

                       

                      I think Let is easier to understand and manage. Unfortunately, I was too lazy.

                      • 8. Re: Need Help with Execute Sql Statement
                        Lemmtech

                        Thank you both your help on this. The context is that I have a portal of related sales orders to a customer record and need a field beside the portal showing their total sales for the past two years.

                        • 9. Re: Need Help with Execute Sql Statement
                          Lemmtech

                          thank you for your help. Sorry I failed to mention that I need the max order date for each customer and not across all records.

                          • 10. Re: Need Help with Execute Sql Statement
                            nicolai

                            Damn. erolst won. Could you flag his post as an answer? Just use his formula, as it references Customer record directly, which is your current context. That was fun, but do you know that you can use a standard FileMaker calculation to do this.

                             

                            SUM(SalesTable::SalesOrderTotal) as a calculation field in the customer table

                            • 11. Re: Need Help with Execute Sql Statement
                              nicolai

                              Adjusting danielfarnan solution:

                               

                              ExecuteSQL ( "

                              SELECT MAX(OrderDate) FROM OrderTable

                              WHERE Type = \"Quote\"

                              AND id_customer = ?";

                              " ; "" ; ""; Customers::id)

                              • 12. Re: Need Help with Execute Sql Statement
                                nicolai

                                If you prefer LET / prettier erolst format:

                                 

                                Let ( [

                                   myQuery ="

                                        SELECT MAX(OrderDate)

                                        FROM OrderTable

                                        WHERE

                                                Type = ?

                                                 AND id_customer = ?";

                                    type= "Quote";

                                    idcustomer=Customers::id

                                  ] ;


                                  ExecuteSQL ( myQuery; "" ; ""; typeidcustomer)

                                )

                                • 13. Re: Need Help with Execute Sql Statement
                                  erolst

                                  nicolai wrote:

                                  ExecuteSQL ( "

                                  SELECT MAX(OrderDate) FROM OrderTable

                                  WHERE Type = \"Quote\"

                                  AND id_customer = ?";

                                  " ; "" ; ""; Customers::id)

                                   

                                  There's a (double) quote too many – if you pardon the pun … (and a semicolon)

                                   

                                  ExecuteSQL ( "

                                    SELECT MAX ( OrderDate )

                                    FROM OrderTable

                                    WHERE

                                      Type = \"Quote\" AND

                                      id_customer = ?

                                    " ; "" ; ""; Customers::id

                                  )

                                  • 14. Re: Need Help with Execute Sql Statement
                                    Lemmtech

                                    Thanks but I keep getting a "Function Not Found Error"  on the Select Max statement. Any thoughts on why this would happen?

                                    1 2 Previous Next