8 Replies Latest reply on Jun 8, 2014 9:37 AM by beverly

    Help with SQL statement

    duncanbaker

      Hi folks

       

      I've been failing miserably trying to write a SQL statement and figured I'd come seeking help... Here's the set up:

       

      I want to obtain the MAX value from a related table, but only where an attribute of that table is a certain value.

       

      Table 1 is INS - this will have the MAXVALUE SQL calc field

      Table 2 is JIQ - this is where the numbers are held that I wish to determine the MAX from. There are multiple records in this table for each record in the INS table.

      But, I only want the MAX value from the records in JIQ when the field TYPE in JIQ = "Type A"

       

      Hope that makes sense. Any help, much appreciated as always.

       

      Thanks

        • 1. Re: Help with SQL statement
          beverly

          What is the query that is not working?

           

          -- sent from my iPhone4 --

          Beverly Voth

          --

          • 2. Re: Help with SQL statement
            duncanbaker

            Err, there currently is no query! What should a query like this look like - I can get half there with SELECT, FROM, WHERE etc but I think I need to JOIN the two tables in the statement?

            • 3. Re: Help with SQL statement
              taylorsharpe

              Assuming you are in a layout where the table occurrence (context) is INS and that the field you want the max from is called "somefieldname" (you didn't tell us its name in your question) and assuming there is a primary key field in INS that relates to a foreign key in JIQ. 

               

              Let ( [

               

              F1 = INS::primarykey ;

              F2 =

                   "SELECT

                        MAX ( JIQ.somefieldname )

                   FROM

                        JIQ

                   WHERE

                        JIQ.foreignkey = ?" ;

              F3 = ExecuteSQL ( F2 ; ¶ ; ¶ ; F1 )

               

              ] ;

               

              F3

               

              )

              • 4. Re: Help with SQL statement
                erolst

                duncanbaker wrote:

                 

                Err, there currently is no query! What should a query like this look like - I can get half there with SELECT, FROM, WHERE etc but I think I need to JOIN the two tables in the statement?

                ExecuteSQL ignores FileMaker relationships; if you want to query the max value only for JIQ records related to the current INS record (what names are that, anyway? ), you need to add that as an additional predicate to the WHERE part; e.g.

                 

                ExecuteSQL ( "

                  SELECT MAX ( aNumberField )

                  FROM JIQ

                  WHERE type = ? AND

                    \"_fk_insID\" = ?

                  " ; "" ; "" ; "A" ; INS::_pk_insID

                  )

                • 5. Re: Help with SQL statement
                  duncanbaker

                  Ah, erolst, that's it. I have spent ALL day with so many variations that were close but not close enough. Thank you, thank you, thank you.

                   

                  Taylor, I appreciate the input also - I see yours is pretty much the same, just incorporating a Let statement.

                   

                  Oh and the table names... I was just abbreviating for here. They are more descriptive than that in the solution!

                   

                  Thanks guys.

                  • 6. Re: Help with SQL statement
                    taylorsharpe

                    Any chance you're coming to Devcon?  I bet there will be some fun sessions on ExecuteSQL. 

                    • 7. Re: Help with SQL statement
                      duncanbaker

                      I will be attending DevCon and looking forward to it. There'll no doubt be a barrage of info to consume.

                      • 8. Re: Help with SQL statement
                        beverly

                        Without you giving us something we can go all wrong with answers! It's helpful to post whatever you got. If it was correct you would not be posting.

                         

                        Try this calculation in INS:

                         

                             ExecuteSQL ( " SELECT MAX(whateverfield) FROM JIQ WHERE JIQ.TYPE = 'Type A' AND JIQ.myID = ? " ; "" ; "" ; INS::myID)

                         

                        since I don't know what field you need to get for MAX, I used "whateverfield". Since I don't know the KEY field match from INS to JIQ, I'm using "myID" in both tables.

                         

                        The above calc is the same as this, but this may be easier to read:

                         

                        Let (

                        [ query = " SELECT MAX(whateverfield)

                        FROM JIQ

                        WHERE JIQ.TYPE = 'Type A'

                        AND JIQ.myID = ? "

                         

                        ; result = ExecuteSQL ( query ; "" ; ""

                             ; INS::myID)

                         

                        ]; result

                        )

                         

                         

                        1. the query is set up

                        2. the query is processed, passing the current records key field as  parameter

                        3. the result of the query is returned.

                         

                        If you get "?" as a result, then there is more you're not telling us, perhaps?

                         

                        Beverly