Goodmorning,
I'm having a little bit of bother trying to update using lead/rank.
Summary of issue: My table holds duplicated data ( only difference being 2 fields viewer_org & interest reason) and rather than holding/processing multuple rows i want to assign the viewer org from the duplicate records and then i can hold 1 row, which has a list of org's that can view it.
I've taken a a few of the interest fields here:-
create table copy_test
(
OWNER_ORG varchar(10),
GEN_REC NUMBER(10),
VIEWER_ORG varchar(10),
INTEREST_REASON varchar(10),
col_1 varchar(10),
col_2 varchar(10),
col_3 varchar(10),
col_4 varchar(10)
);
sample data: -
INSERT INTO COPY_TEST (OWNER_ORG ,GEN_REC ,VIEWER_ORG ,INTEREST_REASON ,COL_1 ,COL_2 ,COL_3 ,COL_4 ) VALUES ('5AA' ,12345 ,'5AA' ,'6543' ,'' ,'' ,'' ,'' );
INSERT INTO COPY_TEST (OWNER_ORG ,GEN_REC ,VIEWER_ORG ,INTEREST_REASON ,COL_1 ,COL_2 ,COL_3 ,COL_4 ) VALUES ('5AA' ,12345 ,'5BB' ,'5430' ,'' ,'' ,'' ,'' );
INSERT INTO COPY_TEST (OWNER_ORG ,GEN_REC ,VIEWER_ORG ,INTEREST_REASON ,COL_1 ,COL_2 ,COL_3 ,COL_4 ) VALUES ('5BB' ,32165 ,'5CC' ,'430' ,'' ,'' ,'' ,'' );
INSERT INTO COPY_TEST (OWNER_ORG ,GEN_REC ,VIEWER_ORG ,INTEREST_REASON ,COL_1 ,COL_2 ,COL_3 ,COL_4 ) VALUES ('5BB' ,32165 ,'5AA' ,'5430' ,'' ,'' ,'' ,'' );
INSERT INTO COPY_TEST (OWNER_ORG ,GEN_REC ,VIEWER_ORG ,INTEREST_REASON ,COL_1 ,COL_2 ,COL_3 ,COL_4 ) VALUES ('5BB' ,32165 ,'5BB' ,'6543' ,'' ,'' ,'' ,'' );
INSERT INTO COPY_TEST (OWNER_ORG ,GEN_REC ,VIEWER_ORG ,INTEREST_REASON ,COL_1 ,COL_2 ,COL_3 ,COL_4 ) VALUES ('YAA' ,98765 ,'5AA' ,'0' ,'' ,'' ,'' ,'' );
INSERT INTO COPY_TEST (OWNER_ORG ,GEN_REC ,VIEWER_ORG ,INTEREST_REASON ,COL_1 ,COL_2 ,COL_3 ,COL_4 ) VALUES ('YAA' ,98765 ,'5BB' ,'543' ,'' ,'' ,'' ,'' );
Data looks like that: -
select * from copy_test;
OWNER_ORG GEN_REC VIEWER_ORG INTEREST_R COL_1 COL_2 COL_3 COL_4
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
5AA 12345 5AA 6543
5AA 12345 5BB 5430
5BB 32165 5CC 430
5BB 32165 5AA 5430
5BB 32165 5BB 6543
YAA 98765 5AA 0
YAA 98765 5BB 543
Basical we have 3 examples above (disctinct on gen_rec). The 1st example Owner 5AA is a record that both organisation 5AA and 5BB are allowed to view. Hence it existing twice, viewer_org 5AA on 1 row and 5BB on the other. I then need to assign both these organisations against one of the rows. I have thise statement which works to identify (slightly): -
SET LINESIZE 250;
select GEN_REC ,VIEWER_ORG ,INTEREST_REASON,OWNER_ORG,VIEWER_ORG CL_1,
LEAD (VIEWER_ORG,1,0) OVER (PARTITION BY GEN_REC ORDER BY GEN_REC ,VIEWER_ORG ,INTEREST_REASON,OWNER_ORG) as CL_2,
LEAD (VIEWER_ORG,2,0) OVER (PARTITION BY GEN_REC ORDER BY GEN_REC ,VIEWER_ORG ,INTEREST_REASON,OWNER_ORG) as CL_3,
LEAD (VIEWER_ORG,3,0) OVER (PARTITION BY GEN_REC ORDER BY GEN_REC ,VIEWER_ORG ,INTEREST_REASON,OWNER_ORG) as CL_4,
RANK() OVER (PARTITION BY GEN_REC ORDER BY GEN_REC ,VIEWER_ORG ,INTEREST_REASON,OWNER_ORG) rank
from COPY_TEST
order by GEN_REC ,VIEWER_ORG ,INTEREST_REASON,OWNER_ORG;
Gives these results: -
GEN_REC VIEWER_ORG INTEREST_R OWNER_ORG CL_1 CL_2 CL_3 CL_4 RANK
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
12345 5AA 6543 5AA 5AA 5BB 0 0 1
12345 5BB 5430 5AA 5BB 0 0 0 2
32165 5AA 5430 5BB 5AA 5BB 5CC 0 1
32165 5BB 6543 5BB 5BB 5CC 0 0 2
32165 5CC 430 5BB 5CC 0 0 0 3
98765 5AA 0 YAA 5AA 5BB 0 0 1
98765 5BB 543 YAA 5BB 0 0 0 2
This is the outcome I need: -
GEN_REC VIEWER_ORG INTEREST_R OWNER_ORG CL_1 CL_2 CL_3 CL_4
12345 5AA 6543 5AA 5AA 5BB
12345 5BB 5430 5AA
32165 5AA 5430 5BB
32165 5BB 6543 5BB 5BB 5AA 5CC
32165 5CC 430 5BB
98765 5AA 0 YAA
98765 5BB 543 YAA 5AA 5BB
I need the information from the viewer_org field to be placed into CL_1, CL_2, CL_3 or CL_04 depeneding how many duplicate rows their are. (would never be more than 4) The hierarchy of which row I want to update would be which ever row had the highest Interest_reason, although its not a number field, it cant be as '0' is important to hold.
Any ideas guys?