I am a newbie to plsql programming and couldnt figure this out;
Can you please provide a solution for this? I will be grateful. Thanks,
My table and data is below.
My source data is in the columns:
Commodity_key1
Commodity_key2
Commodity_Active_Flag
Commodity_Type
My expected output is in the columns:
Commodity_Final and Update_based_on_active_flag--For a combination of Commodity_key1 and Commodity_key2, we substring each comma delimited commodity types and then compare the commodity types from
Commodity_flag='N' to Commodity_flag='Y'. If the commodity types of Commodity_flag='N' are subset of Commodity_flag='Y', they qualify for ----
Commodity_Final=Commodity Types of Commodity_flag='Y', else they are set to their own commodity types. Commodity Type=ALL means it has all the types.
I will try to be clear on few rows which will cover all the scenarios;
Everything is based on commodity_key1 and commodity_key2, on the table :
This is the active record (Y)---> So automatically it gets Commodity_Final=A,B,C,D,E and Update_based_on_active_flag='Y'
1 2 Y A,B,C,D,E
This is a inactive record(N) but a subset of A,B,C,D, E----> So qualifies for Commodity_Final=A,B,C,D,E and Update_based_on_active_flag='Y'
1 2 N B,C,E
This is a inactive record(N) but not a subset of A,B,C,D, E becuase of R----> So does not qualify and so Commodity_Final=R,A,B,C,D,E
1 2 N R,A,B,C,D,E
This is the active record (Y)---> So automatically it gets Commodity_Final=ALL and Update_based_on_active_flag='Y'
5 6 Y ALL
This is a inactive record(N) but a subset of ALL---> So qualifies for Commodity_Final=ALL and Update_based_on_active_flag='Y'
5 6 N L,M,N
Update_based_on_inactive_flag
This is an inactive record(N) but also not subset of A,B,C,D, E----> So does not qualify for Commodity_Final=A,B,C,D,E and Update_based_on_active_flag='N'
1 2 N R,A
But the above record and below records are inactive, and they the above record is subset of below so, for both Update_based_on_inactive_flag='Y'
1 2 N R,A,B,C,D,E R,A,B,C,D,E
CREATE TABLE CINDY
(
COMMODITY_KEY1 NUMBER,
COMMODITY_KEY2 NUMBER,
COMMODITY_ACTIVE_FLAG VARCHAR2(1 BYTE),
COMMODITY_TYPE VARCHAR2(50 BYTE),
COMMODITY_FINAL VARCHAR2(50 BYTE),
UPDATE_BASED_ON_ACTIVE_FLAG VARCHAR2(1 BYTE),
UPDATE_BASED_ON_INACTIVE_FLAG VARCHAR2(1 BYTE)
);
SET DEFINE OFF;
Insert into CINDY
(COMMODITY_KEY1, COMMODITY_KEY2, COMMODITY_ACTIVE_FLAG, COMMODITY_TYPE, COMMODITY_FINAL,
UPDATE_BASED_ON_ACTIVE_FLAG)
Values
(1, 2, 'Y', 'A,B,C,D,E', 'A,B,C,D,E',
'Y');
Insert into CINDY
(COMMODITY_KEY1, COMMODITY_KEY2, COMMODITY_ACTIVE_FLAG, COMMODITY_TYPE, COMMODITY_FINAL,
UPDATE_BASED_ON_ACTIVE_FLAG)
Values
(1, 2, 'N', 'B,C,E', 'A,B,C,D,E',
'Y');
Insert into CINDY
(COMMODITY_KEY1, COMMODITY_KEY2, COMMODITY_ACTIVE_FLAG, COMMODITY_TYPE, COMMODITY_FINAL,
UPDATE_BASED_ON_ACTIVE_FLAG)
Values
(1, 2, 'N', 'D,A', 'A,B,C,D,E',
'Y');
Insert into CINDY
(COMMODITY_KEY1, COMMODITY_KEY2, COMMODITY_ACTIVE_FLAG, COMMODITY_TYPE, COMMODITY_FINAL,
UPDATE_BASED_ON_ACTIVE_FLAG, UPDATE_BASED_ON_INACTIVE_FLAG)
Values
(1, 2, 'N', 'R,A', 'R,A',
'N', 'Y');
Insert into CINDY
(COMMODITY_KEY1, COMMODITY_KEY2, COMMODITY_ACTIVE_FLAG, COMMODITY_TYPE, COMMODITY_FINAL,
UPDATE_BASED_ON_ACTIVE_FLAG)
Values
(1, 2, 'N', 'Z,R', 'Z,R',
'N');
Insert into CINDY
(COMMODITY_KEY1, COMMODITY_KEY2, COMMODITY_ACTIVE_FLAG, COMMODITY_TYPE, COMMODITY_FINAL,
UPDATE_BASED_ON_ACTIVE_FLAG, UPDATE_BASED_ON_INACTIVE_FLAG)
Values
(1, 2, 'N', 'R,A,B,C,D,E', 'R,A,B,C,D,E',
'N', 'Y');
Insert into CINDY
(COMMODITY_KEY1, COMMODITY_KEY2, COMMODITY_ACTIVE_FLAG, COMMODITY_TYPE, COMMODITY_FINAL,
UPDATE_BASED_ON_ACTIVE_FLAG)
Values
(3, 4, 'N', 'ALL', 'ALL',
'N');
Insert into CINDY
(COMMODITY_KEY1, COMMODITY_KEY2, COMMODITY_ACTIVE_FLAG, COMMODITY_TYPE, COMMODITY_FINAL,
UPDATE_BASED_ON_ACTIVE_FLAG)
Values
(5, 6, 'Y', 'ALL', 'ALL',
'Y');
Insert into CINDY
(COMMODITY_KEY1, COMMODITY_KEY2, COMMODITY_ACTIVE_FLAG, COMMODITY_TYPE, COMMODITY_FINAL,
UPDATE_BASED_ON_ACTIVE_FLAG)
Values
(5, 6, 'N', 'L,M,N', 'ALL',
'Y');
COMMIT;
|