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 tableCREATE 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- PublishTable 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 contentCompany A
Company B
Company B
Company A
I would expect to have 4 different compny names.
JF
Original Message:
Sent: 04-06-2020 01:03 PM
From: Billy Keefer
Subject: SEQLOV and shuffle option
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.
Original Message:
Sent: 04-06-2020 11:32 AM
From: Billy Keefer
Subject: SEQLOV and shuffle option
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?
Original Message:
Sent: 04-06-2020 09:13 AM
From: Jean-Francois Berube
Subject: SEQLOV and shuffle option
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
Original Message:
Sent: 04-06-2020 06:10 AM
From: Billy Keefer
Subject: SEQLOV and shuffle option
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
Original Message:
Sent: 04-03-2020 03:45 PM
From: Jean-Francois Berube
Subject: SEQLOV and shuffle option
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