What function to use for grabbing unique data from tableA.columnA and randomly populating tableB.columnB

[Anon Anon]

Hello,

 

I would like to create some synthetic data in a couple of tables.  The first table is the header/parent table, and the second table is the detail/child table.  If I publish 2 records in the header table, each having a unique name in the Name column, what is the function that I would use to populate the Name column in the detail table using each of those unique names from the header table?

 

Example:

Published synthetic data in the header table contain 2 records with the following columns:

Name | ZipCode

Joe | 12345

Mary | 67891

 

Need to publish synthetic data in the detail table with the following columns:

Name | ZipCode | Address

Joe | 12345 | 333 St. Name St.

Joe | 12345 | 444 St. Name Ct.

Joe | 12345 | 555 St. Name Ln.

Mary | 67891 | 666 St. Name Rd.

Mary | 67891 | 777 St. Name Pl.

 

What function could I use to generate random names in the detail table from the distinct list of names in the header table?  I tried using the randlov(percnull,@sqllist(connection,sql)@) and the randlov(percnull,@sqllist(connection,sql)@)coumn) functions but both aren't returning the data I'm expecting.

 

@randlov(0,@sqllist(T,select distinct Name from Header)@)@

 

Thank you,

Tam

[kapad01]

Hi,

 

1. Please maker sure that the target connection is pointing to the database containing the Header table.

2. Please run the following query in the target SQL window to check if you are getting valid results.

select distinct Name from Header

 

Please log a case with support if the above does not work. A WebEx would be a good option to debug.

 

Thanks,

Aditi

Sr. Support Engineer

[gilta03]

Hi Tam,

 

Did Aditi's suggestions help or did you have any more questions?

 

Best regards,

Taylor

[gilta03]

Hi Tam,

 

Did you still need help on this? If not, can you mark Aditi's answer as correct or tell us how you resolved it?

 

Best regards,

[Anon Anon]

Aditi, thank you for suggestions.  To answer your questions:

1. Yes, the target connection is pointed to the database containing the Header table.

2. Running the single select distinct query does bring back the results but that is not the issue.  The question is, once I get the distinct values returned from the column that I'm querying (let's say the results returned 2 distinct records), what function can I use to uniquely insert each of those records into the Detail table without it duplicating the same record more than once.  So if I say publish 2 records into Detail from Header.A column, how do I ensure that the Detail table will get 2 unique inserts.  Hope this makes sense.

 

Taylor, please see my response above.  I was reminded not to open a case for "how to" questions so please advise on what I should do.

 

Thank you,

Tam

[Sameer_Chandragiri]

Hi Tam,

 

Thanks for the clarification. If I now understand your requirement correctly, I'd suggest two changes:

1. Change randlov to seqlov for the column definition in the Detail table. This will ensure that unique values will be picked up from the Header table (in sequence).

2. In the table count for the Detail table on the publish screen, use something like:

@execsqlcount(S,select * from HEADERTABLE)@

This will ensure that the number of records published for the DETAIL table match the number of records that exist in the HEADER table.

 

Let us know if this answers your questions.

 

Thanks,

Sameer

[gilta03]

Hi Tam, Did Sameer's suggestion help or do you still have any more questions?

[Anon Anon]

Hi Sameer and Taylor,

 

Yes, Sameer's suggestion did line up with my query so that helped.  I had to make some other edits in my query so it looks to be working now.

 

Thank you for the follow up.

Tam