1 Reply Latest reply: Jan 24, 2012 6:49 AM by Dave Riley RSS

    How can I iterate of the results of a select statement

      Hi,

       

      I have the following SELECT

       

       

      SQL SELECT
        name as report_definition_name
      FROM reports WHERE reports.active = 1;
      

       

      My question is how do I iterate over the values of the result set? I want to take the report_definition_name (s) and for each one assign the name to a variable and do something with that.

       

      Thanks in advance

      Panayotis

        • Re: How can I iterate of the results of a select statement
          Dave Riley

          Hi Panayotis,

           

           

          Try the following ...

           

          For i = 1 to NoOfRows('yourtable')

            LET varStatic = FieldValue('report_definition_name',$(i)); //Option 1: uses one variable to be used for actions within the loop

            SET varName = 'var_$(i)';
            LET $(varName) = FieldValue('report_definition_name',$(i)); //Option 2: creates variable for each value so they can be                                                                                           referenced outside of the loop

          next

           

          You will probably need to have distinct row values in the table.  If you have a lot of values, the multiple variables option might not be a good idea.

           

           

          flipside