SEQLOV and shuffle option

[JeanFrancois Berube]

Hi guys,

I'm doing data generation by the TDM GENERATOR. I have a question about the SEQLOV function.

@SEQLOV(PERCNULL, @SQLLIST(CONNECTION, SQL[,S])@)@

According to the documentation, the SEQLOV(PERCNULL, @SQLLIST(CONNECTION, SQL[,S])@) function can shuffle the data when the optional parameter S is specified.

"S - (optional) If the literal argument S is specified then the rows returned by the query are randomly shuffled before returning the first item. Subsequent calls do not re-shuffle."

In my synthetic data generation tests, it seems that shuffle is done at all times and not just the first time.

Here's what I did...

I created an Oracle table with 1 column and 5 rows.

table TEST_SEQLOV
Column: Company
Values:
Company A
Company B
Company C
Company D
Company E

Here is the instruction I defined in the generator:

"@seqlov(0,@sqllist(PORACLE_XXXXXXXX_SCRAMBLE,select company from TEST_SEQLOV,S)@)@"


At the PUBLISH,

- the "Table Count" is 4
- the "Repeat" value is 1
- the "On Duplicate in Data Target" value is EXIT
- the "On Generated Duplicate" value is IGNORE

I would have expected to have a list of 4 different companies with no duplicates but I have the following result:

ROW1
Company B
Company A
Company A
Company B

Is there a problem with the shuffle option in SEQLOV?
Is it me who doesn't understand? (I'm a newbie)

Thanks for you help

JF

[Billy Keefer]

Could you change your source table to include a primary key...keep it simple,  1 2 3 4 etc and rerun your test.   

The options 
 the "On Duplicate in Data Target" value is EXIT
- the "On Generated Duplicate" value is IGNORE

only imply when the primary key is involved, eg  a duplicate primary key do Exit or when generating a duplicate primary key  do Ignore

[JeanFrancois Berube]

Billy,

I did test test you suggested.

I created a primary key.

Rerun the test with these values:
- the "Table Count" is 4
- the "Repeat" value is 1
- the "On Duplicate in Data Target" value is EXIT
- the "On Generated Duplicate" value is IGNORE 

and obtain a result with duplicate data:

Company C
Company A
Company C
Company B

My question was not about the options on duplicate data.

My question is about the S (Shuffle) options on teh SEQLOV function. My understanding is that the shuffle is done at the first invocation (based on documentation) and that afterwards the seqlov selects the lines sequentially. By having 5 lines in my test table and asking for 4 lines, I expected to have 4 different lines (without duplicates).

Is my way of looking at it right?

Thanks for your help

[Billy Keefer]

Yep I agree with your logic.  It worked for me but I ran my quick test on SQL Server.  Let me give it a go on Oracle.  Is there something in particular you are trying to do.  Why not try the Randlov function?

[JeanFrancois Berube]

Randlov will give duplication and when I specify the remove options as "On Generated Duplicate" value the quantity asked is not reach. But this is an another problem :) for which I may open a new discussion.

[Billy Keefer]

Jean-Francois I cannot reproduce what you are seeing.  One thing that I should have been stated is to create a primary key for the source and the target.  Have you got a primary key for the target?  I have 5 rows for a customer table and I have a column, cust_name .  When i use the SEQLOV function as you have indicated I get 4 unique cust_name when I publish this to a target.

[JeanFrancois Berube]

Billy,

My need is, for exemple, to generate a list of 20K unique company names from a table containing 50K.
I found a temporary solution which consists in randomizing the table by a pre-publish action.
But I wonder why option S doesn't work and I don't want to reshuffle the tables every time.

For our test, I redo everything from the Oracle tables creation to the publish data file.
Here's the steps:

1- Create the data table 

CREATE TABLE SCRAMBLE.TEST_SEQLOV(COMPANY VARCHAR2(100 BYTE));
CREATE UNIQUE INDEX SCRAMBLE.IDX ON SCRAMBLE.TEST_SEQLOV(COMPANY);
ALTER TABLE SCRAMBLE.TEST_SEQLOV ADD (
CONSTRAINT IDX
PRIMARY KEY (COMPANY) USING INDEX SCRAMBLE.IDX
ENABLE VALIDATE);
INSERT INTO SCRAMBLE.TEST_SEQLOV(company) VALUES ('Company A');
INSERT INTO SCRAMBLE.TEST_SEQLOV(company) VALUES ('Company B');
INSERT INTO SCRAMBLE.TEST_SEQLOV(company) VALUES ('Company C');
INSERT INTO SCRAMBLE.TEST_SEQLOV(company) VALUES ('Company D');
INSERT INTO SCRAMBLE.TEST_SEQLOV(company) VALUES ('Company E');

2- Create the report table

CREATE TABLE GENERATOR.E00556_RAPPORT(ROW1 VARCHAR2(1000 CHAR));
CREATE UNIQUE INDEX GENERATOR.IDX ON GENERATOR.E00556_RAPPORT(ROW1);
ALTER TABLE GENERATOR.E00556_RAPPORT ADD (
CONSTRAINT IDX
PRIMARY KEY (ROW1) USING INDEX GENERATOR.IDX
ENABLE VALIDATE);

3- Regiter the object GENERATOR.E00556_RAPPORT

4- Set a GENERATOR with the follwing function call

@seqlov(0,@sqllist(PORACLE_ORA867UP_SCRAMBLE,select COMPANY from TEST_SEQLOV,S)@)@

5- Publish

Table Count = 4
Publish to = File
File Type = TXT Tab Delimited
Repeat = 1
On Duplicate in data Target = Exit
On Generated Duplicate = Ignore

6- The resulting file content

Company A
Company B
Company B
Company A

I would expect to have 4 different compny names.

JF

[Billy Keefer]

Thanks for the details in your example.  What version of Datamaker are you running and what version of Oracle?  Just want to make sure our environments are the same

[JeanFrancois Berube]

Oh boy... my mistake... should have tell that at the beginning

CA-TDM version 4.9
Oracle Database 12c Enterprise Edition version 12.2.0.1.0, 64 bit

JF

[JeanFrancois Berube]

to be clear, I'm using the TDM portal not Datamaker.

[Billy Keefer]

Hello Jean-Francois.  I was just preparing a response to say I give up because this is working for me.  I dont know why I assumed you were using Datamaker but for some reason I did.  My mistake.  Switching over to the Portal, I see the problem you are having.  I dont see this problem using Datamaker.  I suggest that you  create a support issue for this problem.

[JeanFrancois Berube]

Hi Billy, 

Thank you for your time.