13 Replies Latest reply on Jul 4, 2016 3:14 AM by nickausaus

    ExecuteSQL with multiple AND conditions

    nickausaus

      Hi all

      I'm  quite new to the ExecuteSQL function in FM and wondering if someone can give me some guidance.

      Basically what I'm trying to do is get a Fee  (number) from a table (Fees_Other) based on three conditions:

       

      1. Fee_Name = 'Registration' (for this case, but I have many more to do...)

      2. Effective From >= Purchase_Date

      3. Effective To <= Purchase_Date

       

      So basically I'm trying to get the applicable fee based on name, and effective dates. It works fine if I just have one Fee and only use the first condition, but I get the '?' result when I add the date clauses. Below is the Field Calculation I have at the moment:

       

      ExecuteSQL ( "SELECT Fee FROM Fees_Other WHERE

      Fee_Name = 'Registration'

      and

      Finance::Purchase_Date ≥ Fees_Other::Effective_From

      and

      Finance::Purchase_Date  ≤  Fees_Other::Effective To

      "

      ; "" ; "")

        • 1. Re: ExecuteSQL with multiple AND conditions
          cruiseqe2

          I think that part of your problem is that you have only specified one table (Fees_Other) in the SELECT line, but you are testing against fields in other tables.

          I believe that you might have to use some form of JOIN in order to match up the records from the Finance table.

          When writing SQL, the SQL engine does not pay any attention to your relationship graph. It doesn't know how the tables Fees_Other and Finance are connected. You will have to tell it that in your JOIN clause.

           

          Whenever I have to write SQL, I use the very useful - and free - SQL Explorer database. It has really helped with the writing of my SQL code.

          • 2. Re: ExecuteSQL with multiple AND conditions
            beverly

            you cannot put the fields in the query (the criteria to match). make use of the ? parameters:

             

            ExecuteSQL (

            " SELECT Fee FROM Fees_Other

            WHERE Fee_Name = ?

            AND ... >= ?

            AND ... <= ? "

            ; "" ; ""

            ; "Registration"; Fees_Other::Effective_From; Fees_Other::Effective_To )

             

            Also I see you using another TABLE "Finance" and the field "Purchase_Date". These would not be in the table called (Fees_Other), so you'll need a JOIN within the query.

             

            ExecuteSQL() does not know your relationships.

            beverly

            • 3. Re: ExecuteSQL with multiple AND conditions
              nickausaus

              Thanks beverly and cruiseqe2 !

              I managed to get it to work with your helpful inputs. I had to create a global field in my Finance table which has the value of the Fee_Name ("Registration") in this case so that I join on the Fee Name (as the tables do not have matching keys otherwise).

               

              Do you happen to  know if I can use something like CARTESIAN join, which would then just allow the tables to be joined in the query but the WHERE and AND causes to do the work? I have a number of Fees and it seems a bit messy to have a global field for each Fee name

               

              ExecuteSQL (

              "SELECT Fee FROM Fees_Other O

              join Finance F

              on O.Fee_Name= F.FFRegName

              WHERE Fee_Name = ?

              AND Effective_From <= ?

              AND Effective_To >= ? "

              ; "" ; ""

              ; "Registration"; Finance::Purchase_Date; Finance::Purchase_Date )

              • 4. Re: ExecuteSQL with multiple AND conditions
                beverly

                No joins (in the relationship graph) are used in the queries.

                You can use variables as well as fields to pass the parameters.

                 

                Are you creating scripts to get the values or is this a calculation of some sort that is using the results elsewhere?

                 

                More information needed, please.

                beverly

                • 5. Re: ExecuteSQL with multiple AND conditions
                  nickausaus

                  Hi beverly

                  I'm using it in a calculated field (in the 'Finance' table).

                  This table basically contains all the information relating to leasing a vehicle.

                   

                  In the Fees_Other are a number of different Fees and their Effective From and To dates.

                   

                  So the goal is to get the fee $ value from Fees_Other based on the Fee Name and the Purchase_Date in the Finance table.

                  I understand now that the graph relationships are not used in the ExecuteSQL.

                   

                  I needed something to do the JOIN on so I created the global field in the Finance table so that I can join via matching values, i.e.

                   

                  O.Fee_Name= F.FFRegName

                   

                  It's working fine, I was just wondering if there was a way to avoid the use of the FFRegName (global field), similar to how cartesian joins work in the Relationships Graph.

                   

                  Hope that makes sense?

                  • 6. Re: ExecuteSQL with multiple AND conditions
                    beverly

                    why not just use the F.FFRegName?

                    beverly

                    • 7. Re: ExecuteSQL with multiple AND conditions
                      nickausaus

                      Hi beverly I am for now. I just wondered if there was a better way

                       

                      I actually noticed some weird behaviour now, as I add new records in Finance table, the calculation gives me multiple values. So on the third Finance record, the value in Fee is:

                      15

                      15

                      15

                      Which means instead of $15, the value in the field is treated as 151,515

                       

                      Any Idea what I'm doing wrong? :S

                      • 8. Re: ExecuteSQL with multiple AND conditions
                        beverly

                        You will get as many values as there are "records" that would meet the match. If you have 3 with the same name in the date range, the query returns those as text with a return between the values.

                         

                        If you need just ONE value, then add DISTINCT just after the SELECT. However that may not be accurate either. What native find would get you a unique value? Build the query based on that criteria.

                         

                        beverly

                        • 9. Re: ExecuteSQL with multiple AND conditions
                          user19752

                          You can list tables in FROM clause then the tables are joined as cartesian.

                          SELECT * FROM a,b

                          results all combination of records in table a and b.

                          • 10. Re: ExecuteSQL with multiple AND conditions
                            nickausaus

                            Thanks once again beverly

                            Using the distinct gets me the result I want (based on initial testing). My SQL is very rusty, and I'm just trying this out as an alternate method - I know I can get the same results with lots of TO's and relationships but feel like trying something new

                             

                            To answer your question, only one record in the Fees_Other table should match my criteria. For example I might have in that table

                             

                            ID     Fee_Name     Effective_From     Effective_To     Rate

                            1       Registration     1/1/2016          31/12/2016          15.00

                            2       Registration     1/1/2017          31/12/2017          17.00

                             

                            And in 'Finance' table

                             

                            ID     Purchase_Date     Registration_Fee          F.FFRegName

                            1      2/7/2016               15.00 (goal!)                   "Registration"

                             

                            I may be using the wrong type of join (may need to specify inner/outer?)

                            • 11. Re: ExecuteSQL with multiple AND conditions
                              beverly

                              you should use "=" rather than ">=" and "<=" on the date 'matches'?

                              beverly

                              • 12. Re: ExecuteSQL with multiple AND conditions
                                user19752

                                As already mentioned, there need no relation / join. You are finding a result from a table using 3 conditions.

                                 

                                ExecuteSQL (

                                "SELECT Fee Rate FROM Fees_Other

                                WHERE Fee_Name = ?

                                AND Effective_From <= ?

                                AND Effective_To >= ? "

                                ; "" ; ""

                                ; Finance::F.FFRegName; Finance::Purchase_Date; Finance::Purchase_Date )

                                 

                                Dot in field name is not preferred, it is used as table/column separator in SQL. It is only your miss on copying from the query to example.

                                • 13. Re: ExecuteSQL with multiple AND conditions
                                  nickausaus

                                  Thanks user19752

                                  I had to move ahead with other parts of the solution but will come back to the fees section soon and will try out your method