Red Gate forums :: View topic - Oddball Feature Request: Generation of Temporary Column, etc
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Data Generator 1
SQL Data Generator 1 forum

Oddball Feature Request: Generation of Temporary Column, etc

Search in SQL Data Generator 1 forum
Post new topic   Reply to topic
Jump to:  
Author Message
DAndres109@aol.com



Joined: 03 May 2008
Posts: 14
Location: New York

PostPosted: Sat May 03, 2008 2:44 am    Post subject: Oddball Feature Request: Generation of Temporary Column, etc Reply with quote

Hi,

Consider the following schema:

Table A:
Col1: int
Col2: int
Col3: int
Col4: int
Total: int

I need to be able to ensure the following invariant is true for a each generated row:

Total = random(min, max)
Random % of Total = (Col1 - Col2) + (Col3 - Col4)

A couple of really great features would be nice here:

1. The Total column can be configurable as a random quantity (already supported, but would need to be extended to support the items below)

2. The Random % of Total is a temporary, configurable value that is used solely for deriving an end result to formulae involving other columns. In this case, it is a percentage of the value picked by #1.

3. Col1 through Col4 will be randomly generated (configurable, possibly by min/max percentage of some column value, would be awesome) and will add up, per a given formula, to the value picked by #2.

I guess what I'm asking for is to be able to generate data on a row-basis as well as the current method, which is by column. To be able to interrelate columns in the same row would be very helpful.

Thanks,

David Andres
Back to top
View user's profile Send private message AIM Address Yahoo Messenger
benhall



Joined: 14 Dec 2007
Posts: 98

PostPosted: Tue May 06, 2008 2:31 pm    Post subject: Reply with quote

Hi David,

Thanks for the post. It is not such a odd feature request.

If I understand correctly, to solve your requirement you want to be able to:

1) Define an expression to generate the data based on other columns in the row. Similar to how you define a computed column in SQL Server?

2) Have a set of rules governing how the data is created for each column on a row by row basis?

I have added your post into our system, and we will review it for our next release. If you could confirm my two points above that would really help us for planning the feature.

Thanks

Ben
Back to top
View user's profile Send private message
DAndres109@aol.com



Joined: 03 May 2008
Posts: 14
Location: New York

PostPosted: Tue May 06, 2008 3:54 pm    Post subject: Reply with quote

Ben,

That's a fair summary of my needs at this point.

Essentially, each row could have a set of variables (randomly valued) that can be referred to by expressions for selected columns, and each column can refer to other columns within the same row (to ensure that the figures match up).

I recently completed a project where I needed to ensure the following:

1. Given a randomly generated total Total T, calculate Quantity X as a percentage (between 0 and 20) of that total.
2. Quantity X is really the end result of a formula involving four table columns (referred to as A, B, C, and D), so I wanted to be able to distribute randomly across these four columns while still maintaining the rule that they must add up to Quantity X.

In this scenario, I first calculated the percentage I needed to obtain Quantity X, and calculated the other values based on random portions of this percent. For example, given:

X %: 15
A %: Random(0, X%)
B %: Random(0, X% - A%)
C %: Random(0, X% - A% - B%)
D %: X% - A% - B% - C%

All of which are then multiplied by Total T to get the values I need.

Hopefully, this can be more generalized then what is described here. I think it is complicated, but seems useful to be able to do for particularly hairy test-data-generation needs.

Thanks,

David
Back to top
View user's profile Send private message AIM Address Yahoo Messenger
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group