I have a table NAME_RD, UK column is like a Primary Key / Unique Identfier of the row and ID is the unique identifier of the individual.
CREATE TABLE NAME_RD
(
ID INTEGER,
NAME_HDR VARCHAR2(200 BYTE),
NAME_VAL VARCHAR2(2000 BYTE),
UK INTEGER
)
--
Insert into NAME_RD
(ID, NAME_HDR, NAME_VAL, UK)
Values
(1, 'FIRST', 'FIRST1', 1);
Insert into NAME_RD
(ID, NAME_HDR, NAME_VAL, UK)
Values
(1, 'LAST', 'LAST1', 2);
Insert into NAME_RD
(ID, NAME_HDR, NAME_VAL, UK)
Values
(1, 'FULL', 'FIRST1 LAST1', 3);
Insert into NAME_RD
(ID, NAME_HDR, NAME_VAL, UK)
Values
(2, 'FIRST', 'FIRST2', 4);
Insert into NAME_RD
(ID, NAME_HDR, NAME_VAL, UK)
Values
(2, 'LAST', 'LAST2', 5);
Insert into NAME_RD
(ID, NAME_HDR, NAME_VAL, UK)
Values
(2, 'FULL', 'FIRST2 LAST2', 6);
Insert into NAME_RD
(ID, NAME_HDR, NAME_VAL, UK)
Values
(3, 'FIRST', 'FIRST3', 7);
Insert into NAME_RD
(ID, NAME_HDR, NAME_VAL, UK)
Values
(3, 'LAST', 'LAST3', 8);
Insert into NAME_RD
(ID, NAME_HDR, NAME_VAL, UK)
Values
(3, 'FULL', 'FIRST3 LAST3', 9);
COMMIT;
ID | NAME_HDR | NAME_VAL |
---|
1 | FIRST | FIRST1 |
1 | LAST | LAST1 |
1 | FULL | FIRST1 LAST1 |
2 | FIRST | FIRST2 |
2 | LAST | LAST2 |
2 | FULL | FIRST2 LAST2 |
3 | FIRST | FIRST3 |
3 | LAST | LAST3 |
3 | FULL | FIRST3 LAST3 |
Now I need to randomly change the values of NAME_VAL col as follows
ID | NAME_HDR | NAME_VAL |
---|
1 | FIRST | FIRST3 |
1 | LAST | LAST3 |
1 | FULL | FIRST3 LAST3 |
2 | FIRST | FIRST1 |
2 | LAST | LAST1 |
2 | FULL | FIRST1 LAST1 |
3 | FIRST | FIRST2 |
3 | LAST | LAST2 |
3 | FULL | FIRST2 LAST2 OR |
ID | NAME_HDR | NAME_VAL |
1 | FIRST | FIRST2 |
1 | LAST | LAST2 |
1 | FULL | FIRST2 LAST2 |
2 | FIRST | FIRST3 |
2 | LAST | LAST3 |
2 | FULL | FIRST3 LAST3 |
3 | FIRST | FIRST1 |
3 | LAST | LAST1 |
3 | FULL | FIRST1 LAST1 |
The order can be any but the FIRST , LAST and FULL names should match their repective individual Ids.
The following SQL gets the randomness correctly where the first last and full names as represented as column headers. I need help to modify this logic to do randomness in the table above:
WITH names AS (
SELECT 0 id, '' firstname, '' lastname FROM DUAL WHERE 1=0 UNION ALL
SELECT 1, 'FNAME0' , 'LNAME0' FROM DUAL UNION ALL
SELECT 2, 'FNAME1' , 'LNAME1' FROM DUAL UNION ALL
SELECT 3, 'FNAME2' , 'LNAME2' FROM DUAL UNION ALL
SELECT 4, 'FNAME3' , 'LNAME3' FROM DUAL UNION ALL
SELECT 5, 'FNAME4' , 'LNAME4' FROM DUAL UNION ALL
SELECT 6, 'FNAME5' , 'LNAME5' FROM DUAL UNION ALL
SELECT 7, 'FNAME6' , 'LNAME6' FROM DUAL UNION ALL
SELECT 8, 'FNAME7' , 'LNAME7' FROM DUAL UNION ALL
SELECT 9, 'FNAME8' , 'LNAME8' FROM DUAL UNION ALL
SELECT 10, 'FNAME9' , 'LNAME9' FROM DUAL UNION ALL
SELECT 11, 'FNAME10' , 'LNAME10' FROM DUAL UNION ALL
SELECT 12, 'FNAME11' , 'LNAME11' FROM DUAL UNION ALL
SELECT 13, 'FNAME12' , 'LNAME12' FROM DUAL UNION ALL
SELECT 14, 'FNAME13' , 'LNAME13' FROM DUAL
) -- names
, n AS (
SELECT /*+ MATERIALIZE */ id
, firstname
, lastname
, ROW_NUMBER()OVER(ORDER BY id) names#
, ROW_NUMBER()OVER(ORDER BY dbms_random.value) n#
FROM names
) -- r
, j AS (
SELECT id
, firstname
, lastname
, names#
, n#
, ROW_NUMBER()OVER(ORDER BY dbms_random.value) j#
FROM n
)
SELECT id
, firstname
, lastname
, CASE WHEN names#-n# < 0
THEN LEAD(firstname,ABS(names#-n#))OVER
(ORDER BY names#)
ELSE LAG(firstname,ABS(names#-n#))OVER
(ORDER BY names#)
END as rname
, CASE WHEN names#-j# < 0
THEN LEAD(lastname,ABS(names#-j#))OVER
(ORDER BY names#)
ELSE LAG(lastname,ABS(names#-j#))OVER
(ORDER BY names#)
END as rlastname
FROM j
ORDER BY names#;
Thanks A lot in advance