5 Replies Latest reply on Dec 17, 2013 10:53 AM by philmodjunk

    Record count returned from SQL ?

    JimBessette

      Title

      Record count returned from SQL ?

      Post

           I'm not looking for Count (*) though if it's the only way, perhaps I'd try something that works.   I'm simply looking for the number of records returned from my SQL query.  Can't find anything, or any variable (or any other mention really of it) anywhere.

           Thanks, Jim

        • 1. Re: Record count returned from SQL ?
          philmodjunk

               But Count ( * ) will return the number of records like you want, so I don't understand why you wouldn't use that.

          • 2. Re: Record count returned from SQL ?
            JimBessette

                 Well, maybe I am just missing something, but how do I get a count returned AND the result?   If I have an SQL statement that returns rows, how do I just add that count of # of rows returned?

            • 3. Re: Record count returned from SQL ?
              philmodjunk

                   I haven't tried it, but I've assumed that:

                   Select Count ( * ) , Field1, Field2 ... FROM

                   would put the count in the first column.

                   And if you want it separate from the data shown in the field, you could use:

                   Let ( [Results = ExecuteSQL( query goes here ) ;
                           $$Count = ValueCount ( Results )
                          ];
                            Results )

                   Then you can put <<$$Count>> on your layout to show the record count in the results field.

                   Note: This assumes that you are using ¶ as the record separator.

              • 4. Re: Record count returned from SQL ?
                JimBessette

                     Thanks Phil,

                     Select Count  ( * ) doesn't work.

                     The Let works, but the Merge Variable doesn't get updated when the query is run.   It gets the right data, but it's not updating when the query is run.  It's not optimal either, I really would like a field I can use after the query is run.  

                • 5. Re: Record count returned from SQL ?
                  philmodjunk

                       Yes, you'd need to use refresh window to see the merge variable update on your layout.

                       Otherwise, it looks like you should use two ExecuteSQL function calls, one with Count (*) and one without.

                       Hmmm, just brainstorming some more, but those two queries could be included in the same calculation such that the count is the first or last row of data shown in the field...