7 Replies Latest reply on Jan 26, 2017 8:20 AM by padje

    executeSQL statement question

    padje

      Could someone help me with the following SQL statement ?  Thank you so much in advance.

       

      This is my statement

       

      ExecuteSQL ( "

       

       

      SELECT  m.Naam_Full_Materiaalcategorie + ' - ' + s.m_StatusStock

      FROM invT03_MAT_Materiaal m

      INNER JOIN  invT06_STOCK_Stock s ON s.id_materiaal = m.ID_Materiaal

       

      WHERE s.id_transaktie = ?

       

      AND

      (s.id_StatusStock = 33

      OR s.id_StatusStock = 26

      OR s.id_StatusStock = 28

      OR s.id_StatusStock = 29

      OR s.id_StatusStock = 30)

      "

       

      ; "" ; "" ; C_invT01_ANK_START::ID_Aankoop )

       

       

       

       

       

      The result is

       

       

      Dw - Cymbal boom stand 7700 (hardware drums) - geannuleerd

      Dw - Cymbal boom stand 7700 (hardware drums) - geleverd

      Dw - Cymbal boom stand 7700 (hardware drums) - geleverd

       

       

       

       

       

      I'm looking for this result

       

       

      Dw - Cymbal boom stand 7700 (hardware drums) - geannuleerd (1)

      Dw - Cymbal boom stand 7700 (hardware drums) - geleverd (2)

       

      I only get the question mark when adding GROUP BY and SELECT (COUNT columname), so I must be doing something wrong.

       

      Thanks very much for your help.

        • 1. Re: executeSQL statement question
          Ben

          Hi Padje

           

          If i understand correctly the ExecuteSQL Statment you give works ok, but if you add GROUP BY and SELECT (COUNT columname) it doesn't work?

           

          Have you tried to add just GROUP BY and COUNT separatly? Are they both causing the request to fail?

           

          Can you please supply the Full ExecuteSQL Statements where they fail.

           

          This suggestion will not help you original problem, but you might prefer to use this method at the end of your statement as it is more readable and might be easier to generate in your script:

           

          WHERE s.id_transaktie = ?

          AND s.id_StatusStock IN (33,26,28,29,30)

           

          if you do this you could then use:

           

          WHERE s.id_transaktie = ?

          AND s.id_StatusStock IN (" & $var & ")

           

          all the best

          Ben

          1 of 1 people found this helpful
          • 2. Re: executeSQL statement question
            user19752

            FM SQL doesn't support

            GROUP BY calculation

            so you'd need concatenated calculation field.

            1 of 1 people found this helpful
            • 3. Re: executeSQL statement question
              beverly

              also does not support:

              GROUP BY alias

              GROUP BY #

              So if a concatenated (calculated) column is assigned an alias (AS myCol), it cannot be referenced in the GROUP BY clause as the alias name - ( GROUP BY myCol ), nor by the column number in the SELECT - ( GROUP BY 1 )

               

              if using a calculated field, it would be in the child records (invT06_STOCK_Stock) and no JOIN would be needed for the query, as the parent value would already be in the calculated field. That calculation field could be Count()'ed and included in the GROUP BY.

              beverly

              p.s. Ben has a good point about the IN list of values. That does make an "OR" find.

              2 of 2 people found this helpful
              • 4. Re: executeSQL statement question
                alecgregory

                FMSQL does support GROUP BY, but GROUP BY itself has some requirements.

                 

                Generally, when using GROUP BY you can only have the fields you are grouping by or aggregate functions in the SELECT area of your statement. Yer classic Group By statement is the ranked table. Something like the below

                 

                ExecuteSQL (

                  "SELECT

                    TableName, SUM ( ModCount ) AS ModCountSum

                  FROM

                    FileMaker_Tables

                  GROUP BY

                    TableName

                  ORDER BY

                    ModCountSum DESC";

                  ": ";

                  ""

                )

                 

                But unfortunately it doesn't work with concatenated fields. So the below doesn't work (whether or not you include the alias):

                 

                ExecuteSQL (

                  "SELECT

                    TableName + TableID AS TableNameAndID, SUM ( ModCount ) AS ModCountSum

                  FROM

                    FileMaker_Tables

                  GROUP BY

                    TableNameAndID

                  ORDER BY

                    ModCountSum DESC";

                  ": ";

                  ""

                )

                 

                In your case, you can define a new unstored calculation field in your Naam_Full_Materiaalcategorie table that is the same as the SQL concatenation: m.Naam_Full_Materiaalcategorie + ' - ' + s.m_StatusStock. Obviously you'll need a FileMaker relationship between Naam_Full_Materiaalcategorie and m_StatusStock for this to work. The relationship would have to be based on the same relationship as the join in the sql query, so ID_Materiaal = id_materiaal.

                 

                Then you may be able to get the result you want with the following query:

                 

                ExecuteSQL (

                  "SELECT

                    <The New FileMaker Calculation Field>, ROWID

                  FROM

                    invT03_MAT_Materiaal m

                  INNER JOIN

                    invT06_STOCK_Stock s ON s.id_materiaal = m.ID_Materiaal

                  WHERE

                    s.id_transaktie = ?

                  AND

                    (

                      s.id_StatusStock = 33

                        OR s.id_StatusStock = 26

                        OR s.id_StatusStock = 28

                        OR s.id_StatusStock = 29

                        OR s.id_StatusStock = 30

                    )

                  GROUP BY

                    <The New FileMaker Calculation Field>, ROWID";

                    "";

                    "";

                    C_invT01_ANK_START::ID_Aankoop

                )

                 

                As you'll be working with an unstored calc the query may take a while to run if there's a lot of data in the result.

                3 of 3 people found this helpful
                • 5. Re: executeSQL statement question
                  padje

                  Thanks for the IN suggestion.  Easier to implement indeed.

                  The statement is working indeed as stated.

                   

                  I'm gonna try the suggestion of alecgregory to get the result I want.

                   

                  Thank you all for the input !

                  • 6. Re: executeSQL statement question
                    beverly

                    keep in mind my advice to have the calculated field in the child records and no join needed.

                    beverly

                    • 7. Re: executeSQL statement question
                      padje

                      OK, thanks