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))