Generate Data

I am attempting to populate tables in my database using the generate data option. The function works beautifully except for one seemingly minor problem: I have read in documentation that it is possible to create a list of possible data members for toad to randomly choose from to fill a column in a table. For example, I have a column labeled “SEX” and i want to randomly populate it with ‘M’ or ‘F’ only, (not a random single character). When i use the “constant value” option in the generate data box it only populates with both values together, concatenated, as a single string.

If there is an sql statement that could be used under the sql statement option in that same box, maybe that could be suggested as well.

Any help would be greatly appreciated.

-Luigi Galati

Here is an option: create a secondary dimension table and populate it with two rows - M and F. You can then reference it with a FK type option in the datagen options. This will work ,but there could be a more elegant solution. It will populate random values

Thanks for your response, Don! I see this is an older post, so I'm glad you responded to it. You're correct, that is probably the best method when you're looking to populate from a set of specific values -- especially if the set of possible values can be large. For this situation, where the set of possible values is limited to two, a user could also use something like the following SQL statement to accomplish this as well:

SELECT CASE round(dbms_random.value(1,2)) 
        WHEN 1 THEN 'M' 
        WHEN 2 THEN 'F' 
   END AS Value
FROM dual

Either way should work just fine, but Don's solution is probably the best if you're looking to populate from a set of source values larger than just a few.

-John