11 Replies Latest reply on Aug 18, 2016 7:10 PM by user19752

    Executesql() custom sort

    justinc

      Is there a way to get the results of an ESQL statement to be sorted by a custom-defined order?  I have a table that is used to define a value list.  I'm making a graph of data that uses that value list to define a 'State' value.  I would like the graph labels to show up in the same order as they are listed in the table, which is not alphabetical.  This external table for the value list employs the invisible BOM trick to sort the values in a particular way, based on the order of the records.  So the order is something like "To Do", "Ready", "In Progress", etc.

       

      I have attempted a few different forms of doing this inside the SQL query itself.  (There are two queries - one to select the labels, one to select the values.)  Some examples suggest using a 'Case...' statement in the "Order By" portion of the query where the names can be manually defined:

      ExecuteSQL (

              "SELECT CurrentStage "

              & " FROM TableA"

              & " WHERE ta_ID = ? "

              & " GROUP BY  CurrentStage "

              & " ORDER BY Case when Currentstage = 'To Do' then 1

                                    when Currentstage = 'Ready' then 2

                                     ... "

          ;"";""; 1234 )

       

       

       

      Another suggestions was to do a join to the value-list table and then sort by that field (yes, they are the same datatype):

      ExecuteSQL (

              "SELECT COUNT(*) "

              & " FROM TableA, VLTable "

              & " WHERE ta_ID = ? AND TableA.CurrentStage = VLTable.Stage "

              & " GROUP BY  CurrentStage "

              & " ORDER BY VLTable.Stage "

      ;"";""; 1234 )

       

       

      But neither of those techniques was working.  Anyone have other suggestions?

       

      Thanks,

      Justin

        • 1. Re: Executesql() custom sort
          beverly

          UNION or multiple queries?

          beverly

          • 2. Re: Executesql() custom sort
            user19752

            You can sort with calculated column, but it makes a bit hard to used in graph

                    "SELECT CurrentStage, "

                    & "Case when Currentstage = 'To Do' then 1

                                          when Currentstage = 'Ready' then 2

                                           ... "

                    & " FROM TableA"

                    & " WHERE ta_ID = ? "

                    & " GROUP BY  CurrentStage "

                    & " ORDER BY 2"

             

            Need some calculation or costom function to remove 2nd column.

            • 3. Re: Executesql() custom sort
              fmpdude

              Maybe you need to use the DBMetaData class in a JDBC connection. That way, you can get all the table metadata up front. Then build your queries.

               

              Or create a table with the order of the fields you want and create a script to build your SQL from that table?

               

              Perhaps a SQL plug-in would give you more power as an alternative.

               

              HOPE THIS HELPS.

              • 4. Re: Executesql() custom sort
                coherentkris

                if you need custom sort you can always write a sort script that is performed after the sql call or create a custom function to do the sort.

                • 5. Re: Executesql() custom sort
                  DavidJondreau

                  You could...

                   

                  a) not use ExecuteSQL() but old fashioned finds and sorts.

                   

                  b) create a calc field that takes the value list field and makes it into a numeric result that will sort correctly. You will have to create a table with value and sort fields to hold the value list, or hard code the sorting into the calc (or custom function). ie Case ( value = "Blue" ; 1 ; value = "Red" ; 2 ; value = "Green" ; 3 ). Sort on that new field.

                  • 6. Re: Executesql() custom sort
                    justinc

                    Thanks all for the various replies.  Some of them I'm not sure I understood...But it seems I had half of my answer in my original posting; the other half of inspiration came from reading your replies.  Specifically User19752 statement about a custom function to remove the 2nd column.

                     

                    The answer is in the fact that I have the column with sort values in it already, and filled with invisible characters:  the BOM field in the value list table.  So I included that in my query and sorted on that field.  It turns out fairly similar to one I had tried earlier.  Here's the query I have:

                     

                    ExecuteSQL (

                            "SELECT A.CurrentStage, VL.SortBOM, COUNT(*) "

                            & " FROM TableA as A, VLTable as VL "

                            & " WHERE A.ta_ID_fk = ? AND A.CurrentStage = VL.Stage "

                            & " GROUP BY  A.CurrentStage, VL.SortBOM "

                            & " ORDER BY 2"

                    ;"";""; 1234 )

                     

                    I will still have to break it apart into individual axes, but once I have the query done I don't think that'll be difficult.

                     

                    --  Justin

                    • 7. Re: Executesql() custom sort
                      justinc

                      Hmmm...further refinement:  don't need the BOM in the returned results, just put it in the ORDER BY clause.  BUT...it appears you then still have to throw it into the GROUP BY area also.  Maybe that's where I ran into trouble before - I hadn't included the field I was ordering by in the GROUP BY area.  I thought that you only had to GROUP BY fields in the SELECT that weren't aggregated.

                      1 of 1 people found this helpful
                      • 8. Re: Executesql() custom sort
                        beverly

                        I found with ExecuteSQL that using the column number is either unreliable or unusable. I just call the column by name again for sorting (ORDER BY VL.SortBOM, not ORDER BY 2).

                        beverly

                        2 of 2 people found this helpful
                        • 9. Re: Executesql() custom sort
                          beverly

                          Yes, there are quirks that are not in other versions of SQL. Sorry.

                          beverly

                          • 10. Re: Executesql() custom sort
                            justinc

                            Thanks for the hint.

                            • 11. Re: Executesql() custom sort
                              user19752

                              That is standard behavior.

                               

                              In this case, when you get COUNT(*), GROUP BY need only SortBOM

                                      "SELECT COUNT(*) "

                                      & " FROM TableA as A, VLTable as VL "

                                      & " WHERE A.ta_ID_fk = ? AND A.CurrentStage = VL.Stage "

                                      & " GROUP BY VL.SortBOM "

                                      & " ORDER BY VL.SortBOM"

                               

                              and for getting label, I recalled that how to sort a list according to value list

                              FilterValues (

                              ValueListItems ( "" ; "yourValuelistName" ) ;

                              ExecuteSQL ( "SELECT CurrentStage FROM ..." ) //You don't need sort in SQL

                              )