All Forums Database
vs140580 1 post Joined 12/15
16 Dec 2015
Help to permute records of a table and update into the same table (from mathematics background)
CREATE TABLE "SYSTEM"."SAMPLE" 
   ("E1" VARCHAR2(20 BYTE), 
    "E2" VARCHAR2(20 BYTE), 
    "E3" VARCHAR2(20 BYTE), 
    "E4" VARCHAR2(20 BYTE)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM" ;

 
E1     E2      E3     E4

[0,2] [1,2] [2,3] [3,4]
[0,2] [1,2] [2,4] [3,4]
[0,3] [1,3] [2,3] [2,4]

Remaing data csv is attached.
Help to write query as i am from matematics background.
one tuple in the file the file row will be treated as variable names tab is the delimiter.
E1      E2    E3     E4
[0,2] [1,2] [2,3] [3,4]

Now if one such row exists in the file the i want to make all its shuffles names 24 in number 4! like 

E1     E2     E3    E4
[1,2] [0,2] [3,4] [2,3]

all such 24 shuffles possible for each of the record in table has to be updated in the table.

In this test case i have n=4 variable so 4! shuffles for each row is added to the table.

based on n=number of variables the number of shuffles to be added increases.
 

Result help

To create a sql query to create such shuffles for this sample test data and add in the same 
table as rows and how to generalize it.

Insert command partial as there are 120 records

Insert into SYSTEM.SAMPLE1 (E1,E2,E3,E4) values ('[0,2]','[1,2]','[2,3]','[3,4]');
Insert into SYSTEM.SAMPLE1 (E1,E2,E3,E4) values ('[0,2]','[1,2]','[2,4]','[3,4]');
Insert into SYSTEM.SAMPLE1 (E1,E2,E3,E4) values ('[0,3]','[1,3]','[2,3]','[2,4]');
Insert into SYSTEM.SAMPLE1 (E1,E2,E3,E4) values ('[0,3]','[1,3]','[3,4]','[2,4]');
Insert into SYSTEM.SAMPLE1 (E1,E2,E3,E4) values ('[0,4]','[1,4]','[2,4]','[2,3]');

 

 

You must sign in to leave a comment.