1 2 Previous Next 20 Replies Latest reply on Jul 2, 2014 12:05 PM by wimdecorte

    SQL Query

    mklimow

      Hello,

      I am trying to run a sql script which would take a sum of all of the values that have a certain month and year listed and has a certain condition, and by a certain person.

       

      I have 3 tables, called Tool, FYEngineer, and Users.

       

      I have it setup so it is:

       

      year, Engineer, jan, feb, mar, april, may, jun, ..., dec, total for engineer

       

      inside tool, holds a month, year, budget, and a condition.

       

      and im using the engineer to pull from the tool that they are in.

       

       

      I am trying to pull the budget out when the engineers match, and the condition is a 5, while the month and the year both match what is inside of report. then i want to sum together all of the budgets they have pulled out.

       

      I am trying to use this script,

       

      Let ( [

       

       

      ~sql = "

      SELECT Sum(A.~field1)

      FROM ~table1 AS A, ~table2 AS B

      WHERE (A.~field3 = B.~field4) AND (A.~field5 = 5) AND (A.~field6 = B.~field7) AND (A.~field2 = ?)

      ";

       

       

      ~sqlQuery = Substitute ( ~sql ;

      [ "~table1" ; _fnSQLTableFieldNames (TOOL::BUDescriptionCapitalTab; "table" ) ];

      [ "~table2" ; _fnSQLTableFieldNames (FYEngineer::April; "table" ) ];

      [ "~field1" ; _fnSQLTableFieldNames ( TOOL::Line1BudgetCapitalTab ; "field" ) ];

      [ "~field2" ; _fnSQLTableFieldNames (TOOL::Line1MonthCapitalTab; "field" ) ];

      [ "~field3" ; _fnSQLTableFieldNames (TOOL::Line1YearCapitalTab; "field" ) ];

      [ "~field4" ; _fnSQLTableFieldNames (FYEngineer::FiscalYear; "field" ) ];

      [ "~field5" ; _fnSQLTableFieldNames (TOOL::ToolConditionRating; "field" ) ];

      [ "~field6" ; _fnSQLTableFieldNames (TOOL::ID_MFMProjectEngineerCapitalTab; "field" ) ];

      [ "~field7" ; _fnSQLTableFieldNames (FYEngineer::ID_Engineer; "field" ) ]

      ) ;

       

       

      ~sqlResult = _fnSQL.debug (

      ExecuteSQL (

      ~sqlQuery ; "" ; "" ;

      GetValue($months;$count)

      )

      )

      ];

       

       

      //If ( ~sqlResult = "?" ; False ; True )

      ~sqlResult

       

       

      )

       

      I have been looking, and i cannot seem to find why i am receiving an empty value.

       

      Thanks,

        • 1. Re: SQL Query
          wimdecorte

          You're WHERE clause looks very weird.  You are not asking for any result from B so you should not reference any fields in B, just pass the values of it in.

           

          What does "_fnSQLTableFieldNames" return?  The name of a field or its value?  It sounds like the field names.

          So instead of using B.~field4 and 7, pass it whatever value is in those fields and only reference table A

          • 2. Re: SQL Query
            mklimow

            _fnSQLTableFieldNames, takes the table and field inside of filemaker and pulls either the fieldname or table name, also for some reason I am still getting an incorrect value for what I am trying to do. I keep getting a blank value when I am running it.

            • 3. Re: SQL Query
              erolst

              Why not simply look what value the variable ~sqlQuery, i.e. the SELECT statement itself, holds? That should give you a clue as to where things go awry.

              1 of 1 people found this helpful
              • 4. Re: SQL Query
                wimdecorte

                Did you strip out B and passing the values in instead?

                • 5. Re: SQL Query
                  mklimow

                  yes I did, It helped, but now I have to find out why it is giving the same values to everyone. I have multiple engineers, so it is for some reason giving everyone a certain amount even though they do not have those values.

                  • 6. Re: SQL Query
                    mklimow

                    Also, am I able to enter in a variable as a subsitute for a field?

                    • 7. Re: SQL Query
                      wimdecorte

                      Yes, variables work too

                      • 8. Re: SQL Query
                        mklimow

                        Okay, the script is working for the first record in the table, but it will not run correctly for the rest of them, they either show a blank record, or incorrect values. I am not sure why this is happening at all. Any Ideas?

                        • 9. Re: SQL Query
                          wimdecorte

                          can you post your current query?

                          • 10. Re: SQL Query
                            mklimow

                            This is the current Query that I have at the moment. Also

                            i set $month to getValue($months;$count), $engineer to FYEngineer::ID_Engineer, and $fy to FYEngineer::FiscalYear, since I though that it was not taking the value from each different engineer record, but it was.

                             

                            Let ( [

                             

                             

                            ~sql = "

                            SELECT Sum(A.~field1)

                            FROM ~table1 AS A, ~table2 AS B

                            WHERE  (A.Line1YearCapitalTab =$fy) AND (A.~field5 = 5) AND ( $engineer = A.~field6)   AND (A.~field2 = ?)

                            ";

                             

                             

                            ~sqlQuery = Substitute ( ~sql ;

                            [ "~table1" ; _fnSQLTableFieldNames (TOOL::BUDescriptionCapitalTab; "table" ) ];

                            [ "~table2" ; _fnSQLTableFieldNames (FYEngineer::April; "table" ) ];

                            [ "~field1" ; _fnSQLTableFieldNames ( TOOL::Line1BudgetCapitalTab ; "field" ) ];

                            [ "~field2" ; _fnSQLTableFieldNames (TOOL::Line1MonthCapitalTab; "field" ) ];

                            [ "~field3" ; _fnSQLTableFieldNames (TOOL::Line1YearCapitalTab; "field" ) ];

                            [ "~field4" ; _fnSQLTableFieldNames (FYEngineer::FiscalYear; "field" ) ];

                            [ "~field5" ; _fnSQLTableFieldNames (TOOL::ToolConditionRating; "field" ) ];

                            [ "~field6" ; _fnSQLTableFieldNames (TOOL::ID_MFMProjectEngineerCapitalTab; "field" ) ];

                            [ "~field7" ; _fnSQLTableFieldNames (FYEngineer::ID_Engineer; "field" ) ]

                            ) ;

                             

                             

                            ~sqlResult = _fnSQL.debug (

                            ExecuteSQL (

                            ~sqlQuery ; "" ; "" ;

                            $month

                            )

                            )

                            ];

                             

                             

                            //If ( ~sqlResult = "?" ; False ; True )

                            ~sqlResult

                             

                             

                            )

                            • 11. Re: SQL Query
                              wimdecorte

                              WHERE  (A.Line1YearCapitalTab =$fy)

                               

                              should be

                               

                              WHERE(A.Line1YearCapitalTab = ? )

                              and pass $fy as a parameter to the ExecuteSQL function call

                               

                               

                              This:

                              AND ( $engineer = A.~field6)

                               

                              is only going to be pick a value from the record you are on, so turn that into a ? too an pass it as a parameter to the function call

                               

                               

                              I am still confused why you use the _fnSQLTableFieldNames custom function on some field names but not others like:

                              WHERE  (A.Line1YearCapitalTab

                              1 of 1 people found this helpful
                              • 12. Re: SQL Query
                                mklimow

                                Well, I was slowly removing them to see if that might be why I was having the issue, but now I have completely removed the fnSQLTableFieldNames function now. I now have this as the SQL, but I am still having problems not getting all of the data.

                                 

                                Let ( [

                                 

                                 

                                ~sqlQuery = "

                                SELECT Sum(A.~field1)

                                FROM TOOL AS A

                                WHERE (A.Line1MonthCapitalTab = ?)  AND  (A.Line1YearCapitalTab =?) AND (A.ID_MFMProjectEngineerCapitalTab = ?)  AND (A.ToolConditionRating = 5)

                                ";

                                 

                                 

                                ~sqlResult = _fnSQL.debug (

                                ExecuteSQL (

                                ~sqlQuery ; "" ; "" ;

                                $month; $fy;$engineer

                                )

                                )

                                ];

                                 

                                 

                                //If ( ~sqlResult = "?" ; False ; True )

                                ~sqlResult

                                 

                                 

                                )

                                • 13. Re: SQL Query
                                  erolst

                                  mklimow wrote:

                                  but I am still having problems not getting all of the data.

                                   

                                  Did you consider my suggestion from post #3?

                                  • 14. Re: SQL Query
                                    wimdecorte

                                    You missed this one:

                                     

                                    SELECT Sum(A.~field1)

                                    1 2 Previous Next