8 Replies Latest reply on Aug 29, 2014 7:48 AM by beverly

    Nested ExecuteSQL?

    CP42Kx07

      Following some interesting ideas in response to another SQL-related question I am attempting to use SQL to create faster find steps in certain scripts.

       

      The aim is to find a set of records that match three criteria (Tx Category = "Asset", Tx Date <= TX__Txns::Tx g_Filter Date, SUM (Tx aQty) > 0) and for the result to be a list containing one field only (Tx ID).

       

      The first two criteria are straightforward. The third one has been set-up as an aggregate (to avoid referencing a FM unstored calc field) when grouped on two other fields (As ID and Pf ID) that have been combined solely for this purpose (Tx AsIDPfID).

       

      Is there a way to combine these two fields for the purposes of GROUP BY in the SQL syntax itself rather than in a stored calc field in FM?

       

      The result (successful) is a list of matching records (Tx AsIDPfID) but of course I really need a list containing a different field (Tx ID) for each record.

       

      Do I need to nest SELECT expressions or is there a far simpler way? It seems that I cannot just add Tx ID to the SELECT line without also adding it to the GROUP BY one which probably reduces execution speed (with no group / sort benefit as Tx ID is a unique key field) and still leaves me having to further process the result to remove the unwanted field...

       

      Thanks

       

       

      CP

       

       

       

      Let (

      [ $query = "

      SELECT tx."Tx AsIDPfID"

      FROM "Tx__Txns" tx

      WHERE tx."Tx Category" = ? AND tx."Tx Date" <= ?

      GROUP BY tx."Tx AsIDPfID" HAVING SUM ( tx."Tx aQty" ) > 0"

      ; $result =

      ExecuteSQL (

      $query ;

      ", " ;

      "¶" ;

      "Asset" ;

      TX__Txns::Tx g_Filter Date )

      ] ;

      $result

      )

        • 1. Re: Nested ExecuteSQL?
          beverly

          Does this work?

           

                    SELECT tx.\"Tx ID\"

                    FROM \"Tx__Txns\" tx

                    WHERE tx.\"Tx Category\" = ? AND tx.\"Tx Date\" <= ?

                    GROUP BY tx.\"Tx ID\"

                    HAVING SUM ( tx.\"Tx aQty\" ) > 0"

          • 2. Re: Nested ExecuteSQL?
            CP42Kx07

            Beverly

             

            Thanks but no, the SUM needs to be based on the combination GROUP BY  Tx AsIDPfID field (Assets that are in a specific Portfolio). The Tx ID field is a unique key for the Tx (Transactions) table and therefore won't provide the correct result.

             

            As you can probably tell, my SQL statement was based on one in your 'Missing FM 12 ExecuteSQL Reference"! Clearly you are the go-to lady for all things FM & SQL but I'm not too good at this and couldn't work out how to create a nested SELECT from the example given there.

             

             

            CP

            • 3. Re: Nested ExecuteSQL?
              user19752

              So try this

               

                        SELECT DISTINCT tx.\"Tx ID\"

                        FROM \"Tx__Txns\" tx

                        WHERE tx.\"Tx Category\" = ? AND tx.\"Tx Date\" <= ?

                        AND tx.\"Tx ID\" IN (

                        SELECT MAX ( tx.\"Tx ID\" )

                        FROM \"Tx__Txns\" tx

                        WHERE tx.\"Tx Category\" = ? AND tx.\"Tx Date\" <= ?

                        GROUP BY tx.\"Tx AsIDPfID\"

                        HAVING SUM ( tx.\"Tx aQty\" ) > 0"

                        )

               

              this will work if all tx.\"Tx ID\" in a group are same value.

              • 4. Re: Nested ExecuteSQL?
                CP42Kx07

                Once again, many thanks for your help. Unfortunately all Tx ID are unique and thus by definition not the same value.

                 

                I've spent some more time on this and I've realised that even if I solve this problem there is unlikely to be any advantage using SQL rather than FM to perform these particular finds. In fact the Perform Find script step is slightly faster. At least I now appreciate that SQL would definitely be a useful tool in other FM situations.

                • 5. Re: Nested ExecuteSQL?
                  beverly

                  And my #1 advice on trying to create the SQL statement is to think what it would take to actually do the same in native relationships, finds, and perhaps scripting (ala Virtual List and other techniques).  Often these lead to the ways that a query might be structured, should you decide it a viable alternative.

                   

                  Beverly

                  • 6. Re: Nested ExecuteSQL?
                    user19752

                    Hmm, I lost your point. When Tx ID are unique in the table, which record (value) do you want to get in each group ?

                    • 7. Re: Nested ExecuteSQL?
                      CP42Kx07

                      I am now trying a different (non-SQL) approach but for the record the original issue was as follows:

                       

                       

                      Table: TX

                       

                       

                      Table Occurrences: TX__Txns (primary) & TX_Tx As Pf Date (TX__Txns::Tx As ID = TX_Tx As Pf Date::Tx As ID & TX__Txns::Tx Pf ID = TX_Tx As Pf Date::Tx Pf ID & TX__Txns::Tx g_Filter Date => TX_Tx As Pf Date::Tx Date)

                       

                       

                      Fields: Tx ID (primary key, text), Tx As ID (text), Tx Pf ID (text), Tx Category (text), Tx Date (date), Tx Qty (number), Tx g_Filter Date (date, global) & Tx As Pf Qty (number, calc, unstored, from TX__Txns, = Sum ( TX_Tx As Pf Date::Tx Qty ))

                       

                       

                      Perform Find script criteria: [ Find ] TX__Txns::Tx Category = “Asset” & TX__Txns::Tx Date <= TX__Txns::Tx g_Filter Date [ Omit ] TX__Txns::Tx As Pf Qty = 0

                       

                       

                      The omit criteria ensure that zero quantity records are excluded from a subsequent sub-summary.

                       

                       

                      My aim with the ExecuteSQL statement was to arrive at a Tx ID list for the (equivalent) found set.

                       

                       

                      The problem is that the find is based on an (unavoidable) unstored calc field and is therefore slower than I might wish, hence my interest in whether alternative (e.g. SQL) approaches could help. It (quite logically) appears that the limiting factor in all the approaches that I have tried is the lack of an index.

                      • 8. Re: Nested ExecuteSQL?
                        beverly

                        This information is not helpful to me. If you have a script, print it to PDF and/or screenshot for attachment to this thread. Screen shot the relationship graph  (which makes NO difference in ExecuteSQL).

                         

                        • and if I'm reading any of this correctly, your "[Omit]" must be the last step(s) in any find (manually or scripted). It looks like maybe you are running the criteria inside the Perform Find. I like to break the scripted Find out:

                         

                             Enter Find Mode (no pause!)

                             Set Field ( .....

                             Set Field ( .....

                             Set Field ( ....

                             ....

                             Set Field ( ...

                             Omit

                             Set Field ( ...

                             Omit

                             Perform Find

                         

                        it is very easy to see (and thus edit) what you are doing in the Find.

                         

                        • maybe the "TX__Txns::Tx As Pf Qty=0" and omit really needs to be "TX__Txns::Tx As Pf Qty>0" without the omit.

                         

                        • I'd like to know why you have an unstored ("unavoidable") that can't be stored or scripted or ???. That might be a clue to helping, also.

                         

                        • In ExecuteSQL function, you can concatenate values and use the alias for sorting or grouping:

                         

                             SELECT field1||field2 AS field1_2

                             FROM table

                             WHERE ...

                             ORDER BY field1_2

                         

                        • GROUP BY is typically used when trying to get values along with summary (or other aggregate) values and you are not performing any of those SQL functions.

                         

                        Beverly