Skip to Content

Return a random row from a table in Jitterbit App Builder

Here is a quick How-To guide that allows you to return a random row from any table, which may be useful for sampling in testing:

  1. In the Business Logic Layer, create a Business Object that generates row numbers over the Primary Key(s) of the desired table.

    • For example: Customer (Generate Row Number) is an object which selects the table PK and has an Expression value that generates the row number with this type syntax:

      ${ROW_NUMBER() OVER(ORDER BY C.CustomerID)}

  2. Create another Business Object that selects the Max row number using the results from step 1

    • For example: Customer (Max Row Number) is an object which uses the object from step 1, and has an Expression value that provides the Max Row Number value with this type syntax:

      MAX(CGRN.RowNum)

  3. Create a new Business Object that combines the objects created in Step 1 and 2, and then use the RAND() function to generate a number from 1 to that Max number, and filter where the random number generated = row number. Cross Join on the two objects used in this Rule.

    • For example: This Rule has the following Columns defined:

      • ROW.CustomerID
      • MAX.MaxRowNum
      • ${FLOOR(RAND() * (MAX.MaxRowNum + 1))