Data generation depending on other column

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?

Tagged:

Answers

  • Jessica RJessica R Posts: 1,316 Rose Gold 4
    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!

    Jessica Ramos | Product Support Engineer | Redgate Software

    Have you visited our Help Center?


  • SabineSabine Posts: 2 New member
    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 RJessica R Posts: 1,316 Rose Gold 4
    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

    Jessica Ramos | Product Support Engineer | Redgate Software

    Have you visited our Help Center?


Sign In or Register to comment.