1 2 Previous Next 16 Replies Latest reply on Jul 10, 2014 1:49 PM by monkeybreadsoftware

    ExecuteSQL not working

    Karnel

      I have an ExecuteSQL statement which works completely fine in the Data Viewer, and Developer Assistant, yet when I put the same expression into a field calculation I get nothing--not even a ?. It's not even a very complicated statement.

       

      Let ( id = T05_CONTRIBUTORS::_fkPeopleID ;

       

      ExecuteSQL (

       

      "SELECT "Last"

      FROM T04_PEOPLE

      WHERE PEOPLE_ID = ? ";

       

      "";"";id

       

      ))

       

       

       

      Any suggestions?

        • 1. Re: ExecuteSQL not working
          ChadAdams

          Double check to make sure where you are viewing the calculation field from you have valid data in "id".  I'd simply put the T05_CONTRIBUTORS::_fkPeopleID field on the layout as well and make sure it has data.

           

          Sometimes I get caught up in troubleshooting the SQL, that I forget to look for the obvious and see if there is actually any data to return or if my search criteria is accurate :-)

          • 2. Re: ExecuteSQL not working
            BruceHerbach

            Hi

             

            It sounds like the SQL is working, but that there is no match for id. Make

            sure it has data and that there is a matching record.

             

            Bruce

             

            Sent from my mobile device... Please excuse typos.

            • 3. Re: ExecuteSQL not working
              Karnel

              Thanks for the posts and suggestion!  Definately something to double check and I believe all the data and ids are matching up.  Strangest thing to me is that it evaluates just fine and i get the correct data in the data viewer.  Soo baffled!

               

              7-9-2014 2-23-39 PM.jpg

              • 4. Re: ExecuteSQL not working
                BruceHerbach

                If it works in the data viewer,  the SQL syntax is ok.  Might be something else.  Are you doing this in a script?  If so post the script and let us take a look. 

                • 5. Re: ExecuteSQL not working
                  DanielShanahan

                  I've had similar situations where the field indexes needed to be re-set.  You may want to unindex T04_PEOPLE::Last and T04_PEOPLE::PEOPLE_ID and then re-index them.

                   

                  If you do decide to do this process, I would first unindex those fields and then exit Manage Database.  Then go back into Manage Database and re-index the fields.  I say this because I am ignorant on when a field index actually changes.  Does the actual change occur when making edits on the Storage tab or does it change when exiting Manage Database?  I don't know, so I do the latter.

                  • 6. Re: ExecuteSQL not working
                    pmconaway

                    Does it still work in the data viewer if you are in a different context? Yes, I know that an ExecuteSQL statement should ingore contexts but the data viewer evalutes on context. If you picked a no related layout to your SQL query then does the data viewer.

                     

                     

                    Paul

                    • 7. Re: ExecuteSQL not working
                      beverly

                      The query "context" is not needed, but the "arguments" that are called with table/field DO need context when placed in the calculation dialog.

                       

                      I don't know if this post to fmforums will help. <http://fmforums.com/forum/topic/88426-interesting-quirk-for-unrelated-field-reference-in-field-definition>

                       

                      Beverly

                      • 8. Re: ExecuteSQL not working
                        pmconaway

                        thanks Beverly, that was said much better than I could have. When I'm debugging an issue. I try to take eliminate all possibilities. So in this case my question what is unique to having the query run in the data viewer that is different than when it is in a calculation.

                        • 9. Re: ExecuteSQL not working
                          beverly

                          Probably that 'context' thingy... and :(. We don't have "under the hood" on ExecuteSQL(), so it's just test and speculation from us developers...

                           

                          -- sent from myPhone --

                          Beverly Voth

                          --

                          • 10. Re: ExecuteSQL not working
                            ChadAdams

                            Just to take the context thing out of the equation, hard code the ID and see if that works.  Something like:

                             

                            Let ( id = "102618" ;

                             

                            ExecuteSQL (

                             

                            "SELECT \"Last\"

                            FROM T04_PEOPLE

                            WHERE PEOPLE_ID = ? ";

                             

                            "";"";id

                             

                            ))

                             

                            This will help point to whether you are dealing with a context issue or not.  If the calc above works, then I'd say that when you are viewing the calc it doesn't have access to "T05_CONTRIBUTORS::_fkPeopleID".  If the calc above doesn't work, then I'd say the actual data you are searching for (PEOPLE_ID in the T04_PEOPLE table) has a screwed up index (as Daniel suggests), or doesn't actually match "102618" (perhaps there is a space, or other whitespace character in that data?).

                             

                            Good Luck,

                             

                            Chad

                            • 11. Re: ExecuteSQL not working
                              DanielShanahan

                              While we're on this topic on context, one thing to keep in mind is that you table must have a table occurrence.  I know this sounds obvious, but I fell into this trap.  Once I had a utility table that (virtual list, I believe) that did not require a layout and had no relations, so I did not have it on the Relationship Graph.

                               

                              At the time, I thought that eSQL worked from the raw table, but it appears that it works from - or at least needs - a table occurrence.

                              • 12. Re: ExecuteSQL not working
                                beverly

                                if it's a NUMBER, I would not put quotes around the variable to be sure it's getting the correct value

                                 

                                Let ( id = 102618

                                ...

                                 

                                Beverly

                                • 13. Re: ExecuteSQL not working
                                  beverly

                                  it must have a table occurrence on the graph in the FILE making the sql call.

                                   

                                  You cannot call a table in ExecuteSQL() that is not on the graph. With that, know that the base table IS what is queried, from the standpoint of Table Occurrence's relationship criteria as on the graph is ignored. For example, if you query (from a layout based on Parent) and call up related (child data) that may have constraints in the relationship (not just a parent key match) such as date range, then the ExecuteSQL will not use the constraint  even if you call that table occurrence. It will think it's just asking to search within the full set of child records as if the table had no relationships. - that's what "no context" means

                                   

                                  HTH,

                                  Beverly

                                  • 14. Re: ExecuteSQL not working
                                    DanielShanahan

                                    That is a helpful clarification, Beverly.  Thanks.

                                    1 2 Previous Next