8 Replies Latest reply on Aug 27, 2015 10:28 AM by sccardais

    Execute SQL Distinct

    sccardais

      I want to create a global list of unique years to facilitate searching by year using a checkbox.

       

      I have an ExecuteSQL statement that works in the Data Viewer but doesn't work when entered as an auto-enter calculation for a field.

       

      This is the statement that works in the DataViewer with results that are almost instant

       

      ExecuteSQL ("SELECT DISTINCT c_Year FROM Transactions";

      "";

      "")

       

      c_Year is a text field with a text result.

       

      g_Year is a global text field with the ExecuteSQL statement above as the AutoEnter with calculation.

       

      If I configure the field as a calculation field with this SQL statement, it works for takes forever to complete as I have almost 10,000 transactions. The calculation does not work if the field is Text with this SQL  as the AutoEnter with calculation.

       

      What could be going on here?

        • 1. Re: Execute SQL Distinct
          erolst

          sccardais wrote:

           

          I want to create a global list of unique years to facilitate searching by year using a checkbox.

           

          So why not simply create a value list from the original field in Transactions – provided it is indexable … I assume it's something like

           

          Year ( dateTransaction )

           

          and attach that value list to the global field formatted as checkbox.

          • 2. Re: Execute SQL Distinct
            Mike_Mitchell

            What's going on is you're calculating the list 10,000 times - once for each record.

             

            Follow erolst's advice. Dump the SQL and just use the internal index to calculate the value list.

            • 3. Re: Execute SQL Distinct
              sccardais

              You are correct. c_Year is a calculation field, Transactions::Year(Date)

               

              Are you suggesting a new Summary field, "List of" based on c_Year?

               

              If so, this doesn't give me one entry per year (Distinct values). I have about 10,000 transactions starting in 2009. So, I'm trying to create a global field that I can configure as checkboxes to select one or more years for a search.

               

              Is there another way (other than ExecuteSQL (SELECT DISTINCT...) to create a list of unique values based on the contents of another field?

               

              Also - why the heck does the SQL function work in the data view and not as an auto enter calculation?

              • 4. Re: Execute SQL Distinct
                Mike_Mitchell

                What we're saying is just build a value list, based on the Year ( Date ) field. You can then apply the value list to a field as a checkbox list. No need for any additional fields.

                 

                And ExecuteSQL works just fine as an auto-enter calc. You're just demanding a lot of the database, because it has to do it on every single record. The Data Viewer only has to do it once.

                • 5. Re: Execute SQL Distinct
                  erolst

                  sccardais wrote:

                  Are you suggesting a new Summary field, "List of" based on c_Year?

                  No; I suggested a value list, based on the field Transactions::c_Year; a value list by definition has only distinct values.

                  sccardais wrote:

                  If so, this doesn't give me one entry per year (Distinct values).

                  That is correct, but it ain't so.

                  sccardais wrote:

                  Also - why the heck does the SQL function work in the data view and not as an auto enter calculation?

                   

                  Who knows; you said it “didn't work” without giving any details. It's the wrong approach anyway, so …

                  • 6. Re: Execute SQL Distinct
                    sccardais

                    Mike:

                     

                    When I create a new summary field List of c_Year, I get a list of nearly 10,000 lines (one for each record) with many, many duplicates.

                     

                    I’m sure this isn’t what you and erolst are suggesting.

                     

                    How would you create a list of unique values based on c_Year? Note: c_Year is a calculation with text result Year(Date)

                     

                    Thanks for your comments.

                    • 7. Re: Execute SQL Distinct
                      erolst

                      Use

                       

                      File -> Manage -> Value Lists -> New … -> "Use values from field”: Transactions::c_Year

                      • 8. Re: Execute SQL Distinct
                        sccardais

                        Mike and Erolst

                         

                        Got it. FINALLY.

                         

                        I was fixated on calculations, Summary fields: List of, SQL Distinct, etc.

                         

                        Was NOT focused on the easiest and most logical answer.

                         

                        Thanks for your replies.