6 Replies Latest reply on Sep 10, 2013 12:01 PM by mark.geerdes

    Nested SQL problem

    mark.geerdes

      Working on a nested SQL that I think should work, but is giving me wrong data:

       

       

      ExecuteSQL (

       

      "SELECT COUNT (DISTINCT pgc1."C1FD~Date")

      FROM "PGC1~Field Days" pgc1

      WHERE pgc1."C1FD~Date" BETWEEN ? AND ?

      AND pgc1."C1FD~Primary" IN (

      'Height Pole'

      )

      "

      ;

      "" ; "" ;

      ScheduledTimeOff::q1StartDate ; ScheduledTimeOff::q1EndDate ;

      ScheduledTimeOff::id_EmployeeNumber )

       

      gives me 42

       

      ExecuteSQL (

      "SELECT r.workingDaysSQLList

      FROM "@Resources" r

      " ;

      "" ; "" )

       

      gives me this:

       

      Flatbed,Escort,Tillerman,Height Pole,Assem/Disassem,Route Survey,Push Truck,Pull Truck,Load/Unload,Care/Maintenance (In-Field),Off-Duty (In-Field),Repositioning/Travel,Mentoring,Supervision,Training (In-Field),Training (In-Classroom),Shop/Office Work

       

      Note, Height Pole is 3rd in list

       

       

      ExecuteSQL (

       

      "SELECT COUNT (DISTINCT pgc1."C1FD~Date")

      FROM "PGC1~Field Days" pgc1

      WHERE pgc1."C1FD~Date" BETWEEN ? AND ?

      AND pgc1."C1FD~Primary" IN (

      SELECT r.workingDaysSQLList

      FROM "@Resources" r

      )

      "

      ;

      "" ; "" ;

      ScheduledTimeOff::q1StartDate ; ScheduledTimeOff::q1EndDate ;

      ScheduledTimeOff::id_EmployeeNumber )

       

      gives me 0

       

      what's wrong with my nested select? it should work.

        • 1. Re: Nested SQL problem
          wimdecorte

          the main difference that I'm seeing is that in your hard coded example Height Pole is surrounded by single quotes.  The result of the 2nd ExecuteSQL is a string of values without single quotes around each value.

          1 of 1 people found this helpful
          • 2. Re: Nested SQL problem
            mark.geerdes

            I had tried to put single quotes into another field, and then doing the nested select to get to that list, but still came up with 0. In trying to work around this issue, I came up with this:

             

            ExecuteSQL (

            "SELECT COUNT (DISTINCT \"C1FD~Date\")

            FROM \"PGC1~Field Days\"

            WHERE \"C1FD~Date\" BETWEEN ? AND ?

            AND \"C1FD~Primary\" IN ( " & STO » Resources::workingDaysSQLList & " )

            AND \"C1FD~EMPNo\" = ?

            "

            ;

            "" ; "" ;

            ScheduledTimeOff::q1StartDate ; ScheduledTimeOff::q1EndDate ;

            ScheduledTimeOff::id_EmployeeNumber )

             

            This works, but I would still like any ideas on why the nested SELECT didn't work if anyone has any ideas.

             

            Message was edited by: mark.geerdes

            • 3. Re: Nested SQL problem
              greglane

              Hi Mark,

               

              When you use a sub-select  with an IN operator, FileMaker expects a single "value" coming from each record. In your example, you effectively have one big value with comma-separated strings. That would only match records that have the same big value, which probably doesn't exist.

               

              What I'd suggest is creating a category or type field in your resources table and then put each of the values from your comma-separated list into a separate record. Give all of those records the same category value and then your sub-select can use a WHERE clause to return the correct list of values.

               

              Greg

              1 of 1 people found this helpful
              • 4. Re: Nested SQL problem
                mark.geerdes

                okay, fixed the issue. I thought I had tried this once before posting here, but maybe my syntax was wrong. The trick is to make sure you are selecting from multiple records, not one large pre-formatted list in a single field. If you run the nested select on it's own, you should get a return delimited list instead of a comma separated list. Thanks to Greg Lane for making me go back and try doing it this way again. Here is the statement that works for anyone else trying to figure this out:

                 

                 

                ExecuteSQL (

                "SELECT COUNT (DISTINCT \"C1FD~Date\")

                FROM \"PGC1~Field Days\"

                WHERE \"C1FD~Date\" BETWEEN ? AND ?

                AND \"C1FD~Primary\" IN (

                SELECT workingDays FROM \"@WorkingDayList\"

                )

                AND \"C1FD~EMPNo\" = ?

                "

                ;

                "" ; "" ;

                ScheduledTimeOff::q1StartDate ; ScheduledTimeOff::q1EndDate ;

                ScheduledTimeOff::id_EmployeeNumber )

                • 5. Re: Nested SQL problem
                  ch0c0halic

                  Mark,

                   

                  For an optimization of your code I suggest you review this discussion.

                   

                  https://fmdev.filemaker.com/message/122944#122944

                   

                      • Snippet ***

                   

                  Use a nested ExecuteSQL statement instead:

                  ExecuteSQL("

                  SELECT fields

                  FROM table

                  WHERE field1 IN (" ExecuteSQL("SELECT DISTINCT(field) FROM table2";"";"") ")

                  " ; "" ; "" )

                   

                  From my understanding, the top calculation will run the inner SELECT statement for every iteration of the outer SELECT before returning results, whereas the bottom returns the inner ExecuteSQL statement as a list once to be used by all the outer ExecuteSQL statements.

                   

                  This boosts performance considerably.

                   

                      • Snippet ***

                   

                  • 6. Re: Nested SQL problem
                    mark.geerdes

                    I could not get this to work as you described, so I went with my original calculation, but now, looking at this again, I think it might work, but you forgot ampersands in your calculation here:

                     

                    WHERE field1 IN (" ExecuteSQL("SELECT DISTINCT(field) FROM table2";"";"") ")

                     

                    should be this I think:

                     

                    WHERE field1 IN (" & ExecuteSQL("SELECT DISTINCT(field) FROM table2";"";"") & ")