Sequencing

Question 1

Update dimension to add a sequence id column

Answer 1

Alter table xxxDIM ADD (xxxID number(2))

Question 2

Create sequence

Answer 2

Create Sequence xxx_seq_id start with 1 increment by 1 maxvalue 99999999 minvalue 1 nocycle

Question 3

Add sequence to ID column

Answer 3

Update xxxDim set xxxDIM = xxx_seq_id.nextval

Question 4

Add id column to existing tempFact table

Answer 4

Alter table tempfact add (xxxID number(2))

Question 5

Update tempfact to set the sequence id for its id column

Answer 5

Update tempfact tf set tf. xxx_id = (select from y. xxxID from xxxDIM y where y.aaa = tf.aaa)

Question 6

Add sequence using ‘update method’ for 2 records

Answer 6

Update xxxDIM set xxxID = 1 where aaa = ‘…’ and Update xxxxDIM set xxxID = 2 where aaa = ‘…’

Question 7

Update the tempfact sequence id to a sequence using a cursor

Answer 7

Declare cursor junkcursor is select * from junkDim; Begin for junkcursorrec in junkcursor LOOP update tempFact set junkID = junkcursorrec. junkID where aaa = junkcursorrec.aaa and bbb = junkcursorrec. bbb and ccc = junkcursorrec.ccc and ddd = junkcursorrec. ddd; End loop; End

Question 8

Declare a cursor for the junkDIM

Answer 8

Declare cursor junkcursor is select * from junkDim