1 2 3 Previous Next 30 Replies Latest reply on Jul 4, 2017 11:04 AM by laguna92651

    Sorting SQL

    laguna92651

      I have 3 execute SQL statements in a loop that I am using to pull data from table SummaryData. The data is then placed in an array that I am pulling into a C3 chart. I need to sort the SummaryData table by Type, then by Year before I extract the data, I'm assuming I need to sort the data within the execute SQL statements not in the script itself. I've tried using the ORDER BY statement but have not had any luck. Below is one of the 3 execute SQL statements I am using. Everything works fine except for the sorting.

       

      Let ([
      _Data  = _FIELDREF_getField ( SummaryData::Year ; True );
      _Type = _FIELDREF_getField ( SummaryData::Type ; True );
      _Year = _FIELDREF_getField ( SummaryData::Year ; True );
      _Table = _FIELDREF_getTO ( SummaryData::ID ; True );
      
      _Query = "SELECT " & _Data &  " FROM " & _Table &" WHERE " & _Year  & " = ? AND " & _Type & " = ?" 
      ];
      
      
        • 1. Re: Sorting SQL
          wimdecorte

          what result do you get when you try your ORDER BY?  A "?" (which would be a syntax error) or some result but not the one that you'd expect?

          • 2. Re: Sorting SQL
            laguna92651

            I got a "?".

            • 3. Re: Sorting SQL
              wimdecorte

              So it's a syntax error, can you post your syntax?

              • 4. Re: Sorting SQL
                laguna92651
                Let ([
                
                _Data  = _FIELDREF_getField ( SummaryData::Year ; True );
                _Type = _FIELDREF_getField ( SummaryData::Type ; True );
                _Year = _FIELDREF_getField ( SummaryData::Year ; True );
                
                _Table = _FIELDREF_getTO ( SummaryData::ID ; True );
                
                
                
                _Query = "SELECT " & _Data &  " FROM " & _Table &" WHERE " & _Year  & " = ? AND " & _Type & " = ? ORDER BY SummaryData::Year" 
                
                
                ];
                
                
                
                • 5. Re: Sorting SQL
                  laguna92651

                  What I actually want would be this.

                  Let ([
                  
                  _Data  = _FIELDREF_getField ( SummaryData::Year ; True );
                  _Type = _FIELDREF_getField ( SummaryData::Type ; True );
                  _Year = _FIELDREF_getField ( SummaryData::Year ; True );
                  
                  _Table = _FIELDREF_getTO ( SummaryData::ID ; True );
                  
                  
                  
                  _Query = "SELECT " & _Data &  " FROM " & _Table &" WHERE " & _Year  & " = ? AND " & _Type & " = ? ORDER BY SummaryData::Type, SummaryData::Year" 
                  
                  
                  ];
                  
                  ExecuteSQL(_Query ; "";"";  $Year ; $Fruit)
                  
                  )
                  
                  • 6. Re: Sorting SQL
                    wintertj

                    You should be able to use number(s) on the ORDER BY clause to have it sort by the columns referenced by the order in which they occur on the SELECT statement, in your case 2,3 ... without having to explicitly state the name of the column(s) to ORDER BY.

                     

                    ExecuteSQL ( "select Data, Type, Year, Table from your_table order by 2, 3" ; "" ; "" )

                     

                    Also, I can't tell from the above, are Type and Year even being selected in your concatenation?

                    • 7. Re: Sorting SQL
                      laguna92651

                      They are being selected by seperate eSQL statements, I was just putting things together piece meal, trying not to confuse myself.

                      • 8. Re: Sorting SQL
                        wintertj

                        Ok I gotcha... if you are just selecting a single column in each eSQL statement, then just do ORDER BY 1. You can optionally set the sort sequence to ascending or descending by specifying ASC or DEC after the column list.

                        • 9. Re: Sorting SQL
                          laguna92651

                          Is this what you mean? I still get the same result, "?".

                          Let ([
                          _Data  = _FIELDREF_getField ( SummaryData::Year ; True );
                          _Type = _FIELDREF_getField ( SummaryData::Type ; True );
                          _Year = _FIELDREF_getField ( SummaryData::Year ; True );
                          _Table = _FIELDREF_getTO ( SummaryData::ID ; True );
                          _Query = "SELECT " & _Data &  " FROM " & _Table &" WHERE " & _Year  & " = ? AND " & _Type & " = ? ORDER BY 2, 3" 
                          ];
                          ExecuteSQL(_Query ; "";"";  $Year ; $Fruit)
                          )
                          
                          • 10. Re: Sorting SQL
                            beverly

                            Your columns in ORDER BY are FM notation:

                            table::field

                            when they should be SQL notation:

                            table.field

                             

                            But you had the other fields defined so you can just use the _vars in the ORDER BY as well.

                             

                            Sent from miPhone

                            1 of 1 people found this helpful
                            • 11. Re: Sorting SQL
                              wintertj

                              Yep. Might be easier to write it out in data viewer first and hard code the values, then progressively add LET's, concatenations, functions, etc, progressively... so as to be able to pinpoint where It breaks. My brain, at least, works best breaking things into small chunks.

                              • 12. Re: Sorting SQL
                                laguna92651

                                Yes, I've tried it both ways, same result.

                                • 13. Re: Sorting SQL
                                  laguna92651

                                  Will do, I will try breaking it down, I thought it might be an obvious error on my part.

                                  • 14. Re: Sorting SQL
                                    wintertj

                                    Right on. Note, it you are only selecting one column, you can't have more than one column on the order by, regardless of the syntax used on the order by and taking into account Beverly's keen observation about having FileMaker syntax mixed in with SQL syntax on the column referenced on the order by. I may be confused (typically am) but it still looks like you are selecting a single column and trying to order by two columns, one of which doesn't appear in your select statement. Good luck!

                                    1 2 3 Previous Next