Home » SQL & PL/SQL » SQL & PL/SQL » substring compare and update (oracle 11g)
substring compare and update [message #684259] Wed, 28 April 2021 15:33
CindyC
Messages: 6
Registered: April 2021
Junior Member
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;






Previous Topic: how to change a column to a virtual
Next Topic: sql regex_instr
Goto Forum:
  


Current Time: Tue Jun 15 17:19:28 CDT 2021