Data generation depending on other column

Sabine

Hello, we have a customer table and have to store various attributes for the customer data in an extra attribute table (simplified structure):

 CREATE TABLE [Customer].[tAccounts](

               [IDCustomer]                    [bigint] IDENTITY(1,1) NOT NULL,

               [CustomerName]             [nvarchar](200) NOT NULL) 

IDCustomer	CustomerName
5111	Customer 5111
9789	Customer 9789

 The types of attributes defined:

 

CREATE TABLE [Customer].[tAttributeKeys](

               [IDAttributeKey]              [smallint] IDENTITY(1,1) NOT NULL,

               [AttributeName]             [nvarchar](20) NOT NULL,

               [AttributeDataType] [nvarchar](50) NOT NULL)

In [AttributeDataType] ist festgelegt, welcher Datentyp des Wertes des Attributes gespeichert werden soll, z.B. Int, Date: 

IDAttributeKey	AttributeName	AttributeDataType
1	customer since	DATE
2	Number of branches	INTEGER

The Attribute-Table:

CREATE TABLE [Customer].[tAttributes](

               [IDAttribute]                     [bigint] IDENTITY(1,1) NOT NULL,

               [IDAttributeKey]               [smallint] NOT NULL,

               [IDCustomer]                    [bigint] NOT NULL,

               [AttributeValueInt]          [int] NULL,

               [AttributeValueDate]      [date] NULL)

 

The data in [Customer].[tAttributes] should look something like this:

IDAttribute	IDAttributeKey	IDCustomer	AttributeValueInt	AttributeValueDate
10001	1	5111	NULL	2000-05-12
10002	1	9789	NULL	1995-03-17
10003	2	5111	5	NULL
10004	2	9789	16	NULL

 

The test generator must be used to generate values in the AttributeValueInt column or in the AttributeValueDate column (depending on the attribute). The other attribute column in each case receives the value NULL.

 

So far we have not managed to define a project in which values are generated for all attributes in the Table 

[Customer].[tAttributes] for all customers.

 

Who can help? Are there maybe links?

Jessica R

Hi and thanks for your post!

Just to confirm, is the issue that you are getting some NULL entries for those columns, but you don't want any NULL values?

If so, you can set the NULL percentage to 0% in the column generator settings:


However, please let me know if I've misunderstood!

Sabine

Thank you - but to clarify: we need something like a constrait.
One of two nullable columns should be NULL, but NOT both and also NOT none.

or with other words. if one column is NOT NULL the other column MUST be NULL.
BUT: we need both situations
we like to have sometimes column 1 to be NULL and sometime the column 2 should be NULL

which column (1 or 2) MUST have an value depends on column attribute_key in the sample

if IDAttributeKey = 1 THEN AttributeValueInt MUST be NULL (and AttributeValueDate MUST NOT be NULL)
if IDAttributeKey = 2 THEN AttributeValueDate MUST be NULL (and AttributeValueInt MUST NOT be NULL)

Jessica R

Thank you @Sabine!

Apologies as I don't have example code for this, but I believe you should be able to achieve this using a python script (I suspect you would need to use a conditional IF and the random module).

We have some examples of using Python here which can help with getting started: https://documentation.red-gate.com/sdg/using-generators/example-python-scripts