5 Replies Latest reply on Mar 2, 2013 7:49 PM by fmpros

    SQL quandry...

    fmpros

      OK, I give up.... this is probably something stupid but...

       

      Here's my SQL: ExecuteSQL ( "SELECT REPORTS_TIME + ' / ' + REPORTS_SITE + ' / ' + REPORTS_TITLE + ' / ' + REPORTS_PATIENT_NAMESTYLE_FML_CALC FROM REPORTS WHERE REPORTS_DATE = ? AND REPORTS_SITE IN ( ? )" ; "" ; "" ; $dateSelected ; allowedSitesList )

       

      I've dissected this every which way and all vars evaluate properly and the "IN" statement is properly formatted, i.e., ( 'site a', 'site b', etc ).

      I get null back with this syntax BUT if I say "NOT IN" it returns a list to me. But just the full list, I can't drop a list value out and get a change (what I would expect)... the "IN" is evaluating as if it's NULL.

       

      But I KNOW the site names in the table exactly equal the values in the "IN" list. I've evaluated the ExecuteSQL as a text string with a substitute and it is formatting properly.

       

      Any thoughts...

       

      Thanks,

      William

        • 1. Re: SQL quandry...
          taylorsharpe

          What is the value of the allowedSiteList variable?  Formatting is important in these lists. 

          • 2. Re: SQL quandry...
            BruceHerbach

            Is. AllowedSitesIn a field or a variable?  If its a variable the $ is missing, if its a field then it probably needs a fully qualified field name.

             

            If you have advanced you should be able to check the value in the data viewer.

             

            Bruce

            • 3. Re: SQL quandry...
              fmpros

              Thanks,

               

              The allowedSitesList is generated in the same LET calc as the main ExecuteSQL.  It's an ExecuteSQL that grabs a RETURN delimited list of allowed sites that is then parsed to an "IN" list format:

               

              Let (

              [

                   allowedSitesList = ExecuteSQL...etc

                   Then the main query and so on

              ];

              returnVal

              )

               

              Site A

              Site B

              Site C

               

              to

               

              'Site A', 'Site B', 'Site C' and so on. So the "IN" list evaluates in the SQL as ... REPORTS_SITE IN ( 'Site A', 'Site B', 'Site C' ... etc )

              I've tested this in the Data Viewer and it all seems to evaluate properly.

              It's gotta be in the evaluation of the allowedSitesList parameter to the "?" in the SQL statement; the results suggest that nothing is there, hence a full list returned if "NOT IN".

               

              But I can't see any syntax error.  Pieced out in the Data Viewer everything evaluates properly.

               

              William

              • 4. Re: SQL quandry...
                user10625

                You have to do like this:

                 

                ExecuteSQL ( "SELECT REPORTS_TIME + '  /  ' + REPORTS_SITE + '  /  ' + REPORTS_TITLE + '  /  ' + REPORTS_PATIENT_NAMESTYLE_FML_CALC FROM REPORTS WHERE REPORTS_DATE = ? AND REPORTS_SITE IN ( "  & allowedSitesList  &   " )" ; "" ; "" ; $dateSelected )

                 

                For whatever reason, FMP doesn't like the IN lists in a ?  clause.

                 

                 

                 

                Christopher Bailey

                Baytaflow

                Boston, MA

                • 5. Re: SQL quandry...
                  fmpros

                  PERFECT!

                   

                  That was it.  Thank you Christopher, did not think of putting it inline.

                   

                  Thanks everyone.

                   

                  William