Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Randomly Change Names Values with Realistic Names -- DBMS_RANDOM Usage

Kevin_KJan 9 2018 — edited Jan 10 2018

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;

    

IDNAME_HDRNAME_VAL
1FIRSTFIRST1
1LASTLAST1
1FULLFIRST1 LAST1
2FIRSTFIRST2
2LASTLAST2
2FULLFIRST2 LAST2
3FIRSTFIRST3
3LASTLAST3
3FULLFIRST3 LAST3

Now I need to randomly change the values of NAME_VAL col as follows

    

IDNAME_HDRNAME_VAL
1FIRSTFIRST3
1LASTLAST3
1FULLFIRST3 LAST3
2FIRSTFIRST1
2LASTLAST1
2FULLFIRST1 LAST1
3FIRSTFIRST2
3LASTLAST2
3FULL

FIRST2 LAST2

OR

IDNAME_HDRNAME_VAL
1FIRSTFIRST2
1LASTLAST2
1FULLFIRST2 LAST2
2FIRSTFIRST3
2LASTLAST3
2FULLFIRST3 LAST3
3FIRSTFIRST1
3LASTLAST1
3FULLFIRST1 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

This post has been answered by BrunoVroman on Jan 9 2018
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 7 2018
Added on Jan 9 2018
15 comments
2,834 views