oracle query - append and replace

old post which is closed.

https://www.experts-exchange.com/questions/29095233/Oracle-query.html

CREATE TABLE DSS.SAMPLE_TAB4
(
  DIV_NO                NUMBER(2),
  PSPEC_ID              NUMBER(38),
  MODUL_SHORT_NAME      VARCHAR2(10 BYTE),
  MESSAGE               VARCHAR2(4000 BYTE),
  PO_MESSAGE            VARCHAR2(4000 BYTE),
  INSP_MESSAGE          VARCHAR2(4000 BYTE),
  IGNORE_ZEROS          NUMBER,
  DELTA_BUYER_COMMENTS  VARCHAR2(4000 BYTE)
)

Insert into DSS.SAMPLE_TAB4
   (DIV_NO, PSPEC_ID, MODUL_SHORT_NAME, MESSAGE, PO_MESSAGE, 
    INSP_MESSAGE, IGNORE_ZEROS, DELTA_BUYER_COMMENTS)
 Values
   (5, 1636444, 'PO0010', 'Part MS35489-11 is under contract with KLX AEROSPACE SOLUTIONS (Vendor Number: 50306) | Contract Price: $0.15 EA | Lead time: 5 days | Contract Effective Date: 2017-08-07; Contract Expiration Date: 2018-05-31 | Contracts Contact Info: Name: Sascha Wehske | Email: Sascha.Wehske@KLX.com | Contract Ref: LD7023 | 2017-08-08 :SS:

DELTA BUYER COMMENTS: PACKAGE 50 EA PER PACK IDENTIFY PACKAGE WITH PART NUMBER/QUANTITY/UNIT

ATTN DELTA BUYER: Cut and Paste the following DELTA INSPECTION COMMENTS into the PO L/I internal narrative. 

S/L 60 MONTHS FROM DOM', 'PACKAGE 50 EA PER PACK IDENTIFY PACKAGE WITH PART NUMBER/QUANTITY/UNIT', 
    'S/L 60 MONTHS FROM DOM', 323, 'DELTA BUYER COMMENTS: PACKAGE 50 EA PER PACK IDENTIFY PACKAGE WITH PART NUMBER/QUANTITY/UNIT

ATTN DELTA BUYER: Cut and Paste the following DELTA INSPECTION COMMENTS into the PO L/I internal narrative. 

S/L 60 MONTHS FROM DOM');
Insert into DSS.SAMPLE_TAB4
   (DIV_NO, PSPEC_ID, MODUL_SHORT_NAME, MESSAGE, PO_MESSAGE, 
    INSP_MESSAGE, IGNORE_ZEROS, DELTA_BUYER_COMMENTS)
 Values
   (5, 285110, 'PO0010', 'Part MS35489-17 is under contract with KLX AEROSPACE SOLUTIONS (Vendor Number: 50306) | Contract Price: $0.27 EA | Lead time: 5 days | Contract Effective Date: 2017-08-07; Contract Expiration Date: 2018-05-31 | Contracts Contact Info: Name: Sascha Wehske | Email: Sascha.Wehske@KLX.com | Contract Ref: LD7023 | 2017-08-08 :SS:

DELTA BUYER COMMENTS: PACKAGE 25EA PER PACKAGE IDENTIFY PACKAGE WITH PART NUMBER/QUANTITY/UNIT', 'PACKAGE 25EA PER PACKAGE IDENTIFY PACKAGE WITH PART NUMBER/QUANTITY/UNIT', 
    NULL, 323, 'DELTA BUYER COMMENTS: PACKAGE 25EA PER PACKAGE IDENTIFY PACKAGE WITH PART NUMBER/QUANTITY/UNIT');
Insert into DSS.SAMPLE_TAB4
   (DIV_NO, PSPEC_ID, MODUL_SHORT_NAME, MESSAGE, PO_MESSAGE, 
    INSP_MESSAGE, IGNORE_ZEROS, DELTA_BUYER_COMMENTS)
 Values
   (5, 285112, 'PO0010', 'Part MS35489-17 is under contract with KLX AEROSPACE SOLUTIONS (Vendor Number: 50306) | Contract Price: $0.27 EA | Lead time: 5 days | Contract Effective Date: 2017-08-07; Contract Expiration Date: 2018-05-31 | Contracts Contact Info: Name: Sascha Wehske | Email: Sascha.Wehske@KLX.com | Contract Ref: LD7023 | 2017-08-08 :SS:

DELTA BUYER COMMENTS: PACKAGE 25EA PER PACKAGE IDENTIFY PACKAGE WITH PART NUMBER/QUANTITY/UNIT', 'PACKAGE 25EA PER PACKAGE IDENTIFY PACKAGE WITH PART NUMBER/QUANTITY/UNIT', 
    NULL, 323, 'DELTA BUYER COMMENTS: PACKAGE 25EA PER PACKAGE IDENTIFY PACKAGE WITH PART NUMBER/QUANTITY/UNIT');
Insert into DSS.SAMPLE_TAB4
   (DIV_NO, PSPEC_ID, MODUL_SHORT_NAME, MESSAGE, PO_MESSAGE, 
    INSP_MESSAGE, IGNORE_ZEROS, DELTA_BUYER_COMMENTS)
 Values
   (5, 285123, 'PO0010', 'Part MS35489-20 is under contract with KLX AEROSPACE SOLUTIONS (Vendor Number: 50306) | Contract Price: $0.23 EA | Lead time: 7 days | Contract Effective Date: 2017-07-24; Contract Expiration Date: 2019-12-31 | Contracts Contact Info: Name: Sascha Wehske | Email: Sascha.Wehske@KLX.com | Contract Ref: LD7023 | 2017-08-08 :SS:

DELTA BUYER COMMENTS: PACKAGE 100 EA PER PACK IDENTIFY PACKAGE WITH PART NUMBER/QUANTITY/UNIT.', 'PACKAGE 100 EA PER PACK IDENTIFY PACKAGE WITH PART NUMBER/QUANTITY/UNIT.', 
    NULL, 323, 'DELTA BUYER COMMENTS: PACKAGE 100 EA PER PACK IDENTIFY PACKAGE WITH PART NUMBER/QUANTITY/UNIT.');
Insert into DSS.SAMPLE_TAB4
   (DIV_NO, PSPEC_ID, MODUL_SHORT_NAME, MESSAGE, PO_MESSAGE, 
    INSP_MESSAGE, IGNORE_ZEROS, DELTA_BUYER_COMMENTS)
 Values
   (5, 285125, 'PO0010', 'Part MS35489-20 is under contract with KLX AEROSPACE SOLUTIONS (Vendor Number: 50306) | Contract Price: $0.23 EA | Lead time: 7 days | Contract Effective Date: 2017-07-24; Contract Expiration Date: 2019-12-31 | Contracts Contact Info: Name: Sascha Wehske | Email: Sascha.Wehske@KLX.com | Contract Ref: LD7023 | 2017-08-08 :SS:

DELTA BUYER COMMENTS: PACKAGE 100 EA PER PACK IDENTIFY PACKAGE WITH PART NUMBER/QUANTITY/UNIT.', 'PACKAGE 100 EA PER PACK IDENTIFY PACKAGE WITH PART NUMBER/QUANTITY/UNIT.', 
    NULL, 323, 'DELTA BUYER COMMENTS: PACKAGE 100 EA PER PACK IDENTIFY PACKAGE WITH PART NUMBER/QUANTITY/UNIT.');
Insert into DSS.SAMPLE_TAB4
   (DIV_NO, PSPEC_ID, MODUL_SHORT_NAME, MESSAGE, PO_MESSAGE, 
    INSP_MESSAGE, IGNORE_ZEROS, DELTA_BUYER_COMMENTS)
 Values
   (5, 285159, 'PO0010', 'Part MS35489-35 is under contract with KLX AEROSPACE SOLUTIONS (Vendor Number: 50306) | Contract Price: $0.09 EA | Lead time: 5 days | Contract Effective Date: 2017-08-07; Contract Expiration Date: 2018-05-31 | Contracts Contact Info: Name: Sascha Wehske | Email: Sascha.Wehske@KLX.com | Contract Ref: LD7023 | 2017-08-08 :SS:

DELTA BUYER COMMENTS: PKG 1HU PER PK ID PK WITH PN/QTY/UNIT', 'PKG 1HU PER PK ID PK WITH PN/QTY/UNIT', 
    NULL, 323, 'DELTA BUYER COMMENTS: PKG 1HU PER PK ID PK WITH PN/QTY/UNIT');
Insert into DSS.SAMPLE_TAB4
   (DIV_NO, PSPEC_ID, MODUL_SHORT_NAME, MESSAGE, PO_MESSAGE, 
    INSP_MESSAGE, IGNORE_ZEROS, DELTA_BUYER_COMMENTS)
 Values
   (5, 1474256, 'PO0010', 'Part MS35489-6 is under contract with KLX AEROSPACE SOLUTIONS (Vendor Number: 50306) | Contract Price: $0.10 EA | Lead time: 5 days | Contract Effective Date: 2017-08-07; Contract Expiration Date: 2018-05-31 | Contracts Contact Info: Name: Sascha Wehske | Email: Sascha.Wehske@KLX.com | Contract Ref: LD7023 | 2017-08-08 :SS:

DELTA BUYER COMMENTS: 1 HU PER PK

ATTN DELTA BUYER: Cut and Paste the following DELTA INSPECTION COMMENTS into the PO L/I internal narrative. 

SHELF LIFE 180M FROM CURE DATE', '1 HU PER PK', 
    'SHELF LIFE 180M FROM CURE DATE', 322, 'DELTA BUYER COMMENTS: 1 HU PER PK

ATTN DELTA BUYER: Cut and Paste the following DELTA INSPECTION COMMENTS into the PO L/I internal narrative. 

SHELF LIFE 180M FROM CURE DATE');
Insert into DSS.SAMPLE_TAB4
   (DIV_NO, PSPEC_ID, MODUL_SHORT_NAME, MESSAGE, PO_MESSAGE, 
    INSP_MESSAGE, IGNORE_ZEROS, DELTA_BUYER_COMMENTS)
 Values
   (5, 285232, 'PO0010', 'Part MS35489-6 is under contract with KLX AEROSPACE SOLUTIONS (Vendor Number: 50306) | Contract Price: $0.10 EA | Lead time: 5 days | Contract Effective Date: 2017-08-07; Contract Expiration Date: 2018-05-31 | Contracts Contact Info: Name: Sascha Wehske | Email: Sascha.Wehske@KLX.com | Contract Ref: LD7023 | 2017-08-08 :SS:

DELTA BUYER COMMENTS: 1 HU PER PK

ATTN DELTA BUYER: Cut and Paste the following DELTA INSPECTION COMMENTS into the PO L/I internal narrative. 

SHELF LIFE 180M FROM CURE DATE', '1 HU PER PK', 
    'SHELF LIFE 180M FROM CURE DATE', 322, 'DELTA BUYER COMMENTS: 1 HU PER PK

ATTN DELTA BUYER: Cut and Paste the following DELTA INSPECTION COMMENTS into the PO L/I internal narrative. 

SHELF LIFE 180M FROM CURE DATE');
Insert into DSS.SAMPLE_TAB4
   (DIV_NO, PSPEC_ID, MODUL_SHORT_NAME, MESSAGE, PO_MESSAGE, 
    INSP_MESSAGE, IGNORE_ZEROS, DELTA_BUYER_COMMENTS)
 Values
   (5, 285236, 'PO0010', 'Part MS35489-6 is under contract with KLX AEROSPACE SOLUTIONS (Vendor Number: 50306) | Contract Price: $0.10 EA | Lead time: 5 days | Contract Effective Date: 2017-08-07; Contract Expiration Date: 2018-05-31 | Contracts Contact Info: Name: Sascha Wehske | Email: Sascha.Wehske@KLX.com | Contract Ref: LD7023 | 2017-08-08 :SS:

DELTA BUYER COMMENTS: 1 HU PER PK

ATTN DELTA BUYER: Cut and Paste the following DELTA INSPECTION COMMENTS into the PO L/I internal narrative. 

SHELF LIFE 180M FROM CURE DATE', '1 HU PER PK', 
    'SHELF LIFE 180M FROM CURE DATE', 322, 'DELTA BUYER COMMENTS: 1 HU PER PK

ATTN DELTA BUYER: Cut and Paste the following DELTA INSPECTION COMMENTS into the PO L/I internal narrative. 

SHELF LIFE 180M FROM CURE DATE');
Insert into DSS.SAMPLE_TAB4
   (DIV_NO, PSPEC_ID, MODUL_SHORT_NAME, MESSAGE, PO_MESSAGE, 
    INSP_MESSAGE, IGNORE_ZEROS, DELTA_BUYER_COMMENTS)
 Values
   (5, 285258, 'PO0010', 'DELTA BUYER COMMENTS: MUST BE PACKAGED 100 EA(HU) PER PKG.

ATTN DELTA BUYER: Cut and Paste the following DELTA INSPECTION COMMENTS into the PO L/I internal narrative. 

SHELF LIFE 180 MONTHS FROM D.O.M', 'MUST BE PACKAGED 100 EA(HU) PER PKG.', 
    'SHELF LIFE 180 MONTHS FROM D.O.M', 0, ' BUYER COMMENTS: MUST BE PACKAGED 100 EA(HU) PER PKG.

ATTN DELTA BUYER: Cut and Paste the following DELTA INSPECTION COMMENTS into the PO L/I internal narrative. 

SHELF LIFE 180 MONTHS FROM D.O.M');
Insert into DSS.SAMPLE_TAB4
   (DIV_NO, PSPEC_ID, MODUL_SHORT_NAME, MESSAGE, PO_MESSAGE, 
    INSP_MESSAGE, IGNORE_ZEROS, DELTA_BUYER_COMMENTS)
 Values
   (5, 1735456, 'PO0010', 'Part MS35489-6 is under contract with KLX AEROSPACE SOLUTIONS (Vendor Number: 50306) | Contract Price: $0.10 EA | Lead time: 5 days | Contract Effective Date: 2017-08-07; Contract Expiration Date: 2018-05-31 | Contracts Contact Info: Name: Sascha Wehske | Email: Sascha.Wehske@KLX.com | Contract Ref: LD7023 | 2017-08-08 :SS:

DELTA BUYER COMMENTS: 1 HU PER PK

ATTN DELTA BUYER: Cut and Paste the following DELTA INSPECTION COMMENTS into the PO L/I internal narrative. 

SHELF LIFE 180M FROM CURE DATE', '1 HU PER PK', 
    'SHELF LIFE 180M FROM CURE DATE', 322, 'DELTA BUYER COMMENTS: 1 HU PER PK

ATTN DELTA BUYER: Cut and Paste the following DELTA INSPECTION COMMENTS into the PO L/I internal narrative. 

SHELF LIFE 180M FROM CURE DATE');
Insert into DSS.SAMPLE_TAB4
   (DIV_NO, PSPEC_ID, MODUL_SHORT_NAME, MESSAGE, PO_MESSAGE, 
    INSP_MESSAGE, IGNORE_ZEROS, DELTA_BUYER_COMMENTS)
 Values
   (5, 285199, 'PO0010', 'DELTA BUYER COMMENTS: PACKAGE 1 EA PER PACK IDENTIFY PACKAGE WITH PART NUMBER/QUANTITY/UNIT', 'PACKAGE 1 EA PER PACK IDENTIFY PACKAGE WITH PART NUMBER/QUANTITY/UNIT', 
    NULL, 0, ' BUYER COMMENTS: PACKAGE 1 EA PER PACK IDENTIFY PACKAGE WITH PART NUMBER/QUANTITY/UNIT');
Insert into DSS.SAMPLE_TAB4
   (DIV_NO, PSPEC_ID, MODUL_SHORT_NAME, MESSAGE, PO_MESSAGE, 
    INSP_MESSAGE, IGNORE_ZEROS, DELTA_BUYER_COMMENTS)
 Values
   (5, 1474241, 'PO0010', 'DELTA BUYER COMMENTS: MUST BE PACKAGED 100 EA(HU) PER PKG.', 'MUST BE PACKAGED 100 EA(HU) PER PKG.', 
    NULL, 0, ' BUYER COMMENTS: MUST BE PACKAGED 100 EA(HU) PER PKG.');
Insert into DSS.SAMPLE_TAB4
   (DIV_NO, PSPEC_ID, MODUL_SHORT_NAME, MESSAGE, PO_MESSAGE, 
    INSP_MESSAGE, IGNORE_ZEROS, DELTA_BUYER_COMMENTS)
 Values
   (5, 4503225, 'PO0010', 'DELTA BUYER COMMENTS: MUST BE PACKAGED 100 EA(HU) PER PKG.', 'MUST BE PACKAGED 100 EA(HU) PER PKG.', 
    NULL, 0, ' BUYER COMMENTS: MUST BE PACKAGED 100 EA(HU) PER PKG.');
Insert into DSS.SAMPLE_TAB4
   (DIV_NO, PSPEC_ID, MODUL_SHORT_NAME, MESSAGE, PO_MESSAGE, 
    INSP_MESSAGE, IGNORE_ZEROS, DELTA_BUYER_COMMENTS)
 Values
   (5, 285255, 'PO0010', 'DELTA BUYER COMMENTS: MUST BE PACKAGED 100 EA(HU) PER PKG.

ATTN DELTA BUYER: Cut and Paste the following DELTA INSPECTION COMMENTS into the PO L/I internal narrative. 

SHELF LIFE 180 MONTHS FROM D.O.M', 'MUST BE PACKAGED 100 EA(HU) PER PKG.', 
    'SHELF LIFE 180 MONTHS FROM D.O.M', 0, ' BUYER COMMENTS: MUST BE PACKAGED 100 EA(HU) PER PKG.

ATTN DELTA BUYER: Cut and Paste the following DELTA INSPECTION COMMENTS into the PO L/I internal narrative. 

SHELF LIFE 180 MONTHS FROM D.O.M');
Insert into DSS.SAMPLE_TAB4
   (DIV_NO, PSPEC_ID, MODUL_SHORT_NAME, MESSAGE, PO_MESSAGE, 
    INSP_MESSAGE, IGNORE_ZEROS, DELTA_BUYER_COMMENTS)
 Values
   (5, 1502677, 'PO0010', 'Part MS35489-101X is under contract with KLX AEROSPACE SOLUTIONS (Vendor Number: 50306) | Contract Price: $1.17 EA | Lead time: 5 days | Contract Effective Date: 2017-08-07; Contract Expiration Date: 2018-05-31 | Contracts Contact Info: Name: Sascha Wehske | Email: Sascha.Wehske@KLX.com | Contract Ref: LD7023 | 2017-08-08 :SS:', NULL, 
    'SHELF LIFE 228 MONTHS FROM DOM', 325, NULL);
Insert into DSS.SAMPLE_TAB4
   (DIV_NO, PSPEC_ID, MODUL_SHORT_NAME, MESSAGE, PO_MESSAGE, 
    INSP_MESSAGE, IGNORE_ZEROS, DELTA_BUYER_COMMENTS)
 Values
   (5, 2717653, 'PO0010', 'Part MS35489-101X is under contract with KLX AEROSPACE SOLUTIONS (Vendor Number: 50306) | Contract Price: $1.17 EA | Lead time: 5 days | Contract Effective Date: 2017-08-07; Contract Expiration Date: 2018-05-31 | Contracts Contact Info: Name: Sascha Wehske | Email: Sascha.Wehske@KLX.com | Contract Ref: LD7023 | 2017-08-08 :SS:', NULL, 
    'SHELF LIFE 228 MONTHS FROM DOM', 325, NULL);
Insert into DSS.SAMPLE_TAB4
   (DIV_NO, PSPEC_ID, MODUL_SHORT_NAME, MESSAGE, PO_MESSAGE, 
    INSP_MESSAGE, IGNORE_ZEROS, DELTA_BUYER_COMMENTS)
 Values
   (5, 8635155, 'PO0010', 'Part MS35489-4 is under contract with KLX AEROSPACE SOLUTIONS (Vendor Number: 50306) | Contract Price: $0.07 EA | Lead time: 7 days | MOQ: 188 | Contract Effective Date: 2017-07-24; Contract Expiration Date: 2019-12-31 | Contracts Contact Info: Name: Sascha Wehske | Email: Sascha.Wehske@KLX.com | Contract Ref: LD7023 | 2017-08-08 :SS:', 'PACKAGE 100 EA PER PACK IDENTIFY PACKAGE WITH PART NUMBER/QUANTITY/UNIT MUST BE PACKAGED 100 EA(HU) PER PKG.', 
    NULL, 333, NULL);
Insert into DSS.SAMPLE_TAB4
   (DIV_NO, PSPEC_ID, MODUL_SHORT_NAME, MESSAGE, PO_MESSAGE, 
    INSP_MESSAGE, IGNORE_ZEROS, DELTA_BUYER_COMMENTS)
 Values
   (5, 3876811, 'PO0010', 'Part MS35489-101X is under contract with KLX AEROSPACE SOLUTIONS (Vendor Number: 50306) | Contract Price: $1.17 EA | Lead time: 5 days | Contract Effective Date: 2017-08-07; Contract Expiration Date: 2018-05-31 | Contracts Contact Info: Name: Sascha Wehske | Email: Sascha.Wehske@KLX.com | Contract Ref: LD7023 | 2017-08-08 :SS:', NULL, 
    'SHELF LIFE 228 MONTHS FROM DOM', 325, NULL);
COMMIT;

Open in new window


Data I have

message column has the entire message
po_message - new to be appended.
insp_message - new to be appended

delta buyer comments - this is the comments that has to be replaced by po_message and insp_message. Sometimes will have po_message and insp_message will be null
or
insp_message will be there and po_message will be nulll
or
will have both po_message and insp_message.

from the message column  will have to append the delta buyer comments with the new (po_message or insp_message or both with chr(10)

help is really appreciated. Tried so many ways but was not successful

Tried using ,substr(.message,(instr(message,':SS:')+6)) as delta_buyer_comments , but could not succeed
LVL 6
anumosesAsked:
Who is Participating?
 
SujithData ArchitectCommented:
select   regexp_replace(message, '(DELTA BUYER COMMENTS|ATTN DELTA BUYER).*', 'DELTA BUYER COMMENTS:  ',1,1,'n')||
            NVL2(po_message, po_message, NULL)||
            NVL2(insp_message, NVL2(po_message, chr(10)||chr(10), NULL) ||insp_message, null)   str, po_message, insp_message
   from sample_tab6
0
 
slightwv (䄆 Netminder) Commented:
Concerning both questions:
Are you sure that data is not sensitive and allowed to be posted in a public forum?
0
 
anumosesAuthor Commented:
Its sample data. Mimiced the data
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Alexander Eßer [Alex140181]Software DeveloperCommented:
You need to clarify (in detail) what you expect, otherwise we could only guess what you want to achieve...
From what I could guess, maybe you need something like this:
select case
         when instr(a.message, 'DELTA BUYER COMMENTS:') > 0 then
          regexp_replace(a.message,
                         'DELTA BUYER COMMENTS:(.*)',
                         'DELTA BUYER COMMENTS: ' ||
                         nvl2(a.insp_message, nvl2(a.po_message, a.po_message || chr(10) || a.insp_message, a.insp_message), a.po_message),
                         1,
                         1,
                         null)
         else
          a.message || chr(10) ||
          nvl2(a.insp_message, nvl2(a.po_message, a.po_message || chr(10) || a.insp_message, a.insp_message), a.po_message)
       end new_message,
       a.*
  from SAMPLE_TAB4 a;

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
>>Its sample data. Mimiced the data

Sure looks like real part numbers, sales contact, contract information and email addresses.  If I were KLX and/or Sascha, I might not want that information made public.
0
 
anumosesAuthor Commented:
For pspec_id - 1636444

message is

Part MS35489-11 is under contract with KLX AEROSPACE SOLUTIONS (Vendor Number: 50306) | Contract Price: $0.15 EA | Lead time: 5 days | Contract Effective Date: 2017-08-07; Contract Expiration Date: 2018-05-31 | Contracts Contact Info: Name: Sascha Wehske | Email: Sascha.Wehske@KLX.com | Contract Ref: LD7023 | 2017-08-08 :SS:

DELTA BUYER COMMENTS: PACKAGE 50 EA PER PACK IDENTIFY PACKAGE WITH PART NUMBER/QUANTITY/UNIT

ATTN DELTA BUYER: Cut and Paste the following DELTA INSPECTION COMMENTS into the PO L/I internal narrative.

S/L 60 MONTHS FROM DOM

Required

delete

DELTA BUYER COMMENTS: PACKAGE 50 EA PER PACK IDENTIFY PACKAGE WITH PART NUMBER/QUANTITY/UNIT

ATTN DELTA BUYER: Cut and Paste the following DELTA INSPECTION COMMENTS into the PO L/I internal narrative.

S/L 60 MONTHS FROM DOM

and append with

PACKAGE 50 EA PER PACK IDENTIFY PACKAGE WITH PART NUMBER/QUANTITY/UNIT(This is po message)

S/L 60 MONTHS FROM DOM (this is insp message)

IN the above case both po message and insp message are there. Some times either po message or insp message is null then take the one that is not null.

But in the answer you provided message and new message are the same.
0
 
anumosesAuthor Commented:
In the sample table the data I provided for the column delta_buyer_comments is the stripped data from the message column
0
 
anumosesAuthor Commented:
If a solution can be provided how to separate the part message if exists( above the delta buyer comments) I will add both the data into a staging table and then append the new data. I am not able to separate the part message if exists.

eg like the below

Part MS35489-11 is under contract with KLX AEROSPACE SOLUTIONS (Vendor Number: 50306) | Contract Price: $0.15 EA | Lead time: 5 days | Contract Effective Date: 2017-08-07; Contract Expiration Date: 2018-05-31 | Contracts Contact Info: Name: Sascha Wehske | Email: Sascha.Wehske@KLX.com | Contract Ref: LD7023 | 2017-08-08 :SS:
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
select regexp_replace(a.message,
                      ':SS:\s*+(.*)',
                      nvl2(a.insp_message,
                           chr(10) || nvl2(a.po_message,
                                           chr(10) || a.po_message || chr(10) || chr(10) || a.insp_message,
                                           chr(10) || chr(10) || a.insp_message),
                           chr(10) || chr(10) || a.po_message),
                      1,
                      1,
                      'n') new_message,
       a.*
  from SAMPLE_TAB4 a;

Open in new window

0
 
SujithData ArchitectCommented:
Is this what you are looking for? Else provide sample inputs and expected output in the columns

 update sample_tab4
   set delta_buyer_comments = regexp_replace(message, '(DELTA BUYER COMMENTS.*)', 'DELTA BUYER COMMENTS:  THIS IS SOMEWHAT NEW PART THAT HAS TO BE PACKAGED.',1,1,'n')||
            NVL2(po_message, chr(10)||po_message, NULL)||
            NVL2(insp_message, chr(10)||insp_message, null)
   where pspec_id = 1636444
   and div_no = 5
   and modul_short_name = 'PO0010';
0
 
anumosesAuthor Commented:
Almost perfect. but in the new message

:SS: this is missing for part message
0
 
SujithData ArchitectCommented:
Correction to the above:

 update sample_tab4
   set delta_buyer_comments = regexp_replace(message, '(DELTA BUYER COMMENTS.*)', 'DELTA BUYER COMMENTS:  ',1,1,'n')||
            NVL2(po_message, chr(10)||po_message, NULL)||
            NVL2(insp_message, chr(10)||insp_message, null)
   where pspec_id = 1636444
   and div_no = 5
   and modul_short_name = 'PO0010';
0
 
anumosesAuthor Commented:
As I was testing with your code, Alexander, your code works only when part message is there. If part message is not there and it only has delta buyer comments, then it will not work.
0
 
anumosesAuthor Commented:
sujith I am givinfga sample table with 3 lines of data and hope you can help me. Its a very crucial data update as the user is waiting on me
0
 
anumosesAuthor Commented:
CREATE TABLE DSS.SAMPLE_TAB6
(
  DIV_NO            NUMBER(2)                   NOT NULL,
  PSPEC_ID          NUMBER(38)                  NOT NULL,
  MODUL_SHORT_NAME  VARCHAR2(10 BYTE)           NOT NULL,
  MESSAGE           VARCHAR2(4000 BYTE)         NOT NULL,
  PO_MESSAGE        VARCHAR2(4000 BYTE),
  INSP_MESSAGE      VARCHAR2(4000 BYTE)
)


SET DEFINE OFF;
Insert into DSS.SAMPLE_TAB6
   (DIV_NO, PSPEC_ID, MODUL_SHORT_NAME, MESSAGE, PO_MESSAGE, 
    INSP_MESSAGE)
 Values
   (5, 31454, 'PO0010', 'Part AN320-6 is under contract with KLX AEROSPACE SOLUTIONS (Vendor Number: 50306) | Contract Price: $0.60 EA | Lead time: 7 days | Contract Effective Date: 2017-07-24; Contract Expiration Date: 2019-12-31 | Contracts Contact Info: Name: Sascha Wehske | Email: Sascha.Wehske@KLX.com | Contract Ref: LD7023 | 2017-08-08 :SS:

DELTA BUYER COMMENTS: PACKAGE 100 EA PER PK, IDENTIFY PKG WITH PART NBR, QUANTITY/UNIT MUST BE PACKAGED 100 EA(HU) PER PKG.', 'PACKAGE 100 EA PER PK, IDENTIFY PKG WITH PART NBR, QUANTITY/UNIT MUST BE PACKAGED 100 EA(HU) PER PKG.', 
    NULL);
Insert into DSS.SAMPLE_TAB6
   (DIV_NO, PSPEC_ID, MODUL_SHORT_NAME, MESSAGE, PO_MESSAGE, 
    INSP_MESSAGE)
 Values
   (5, 1662336, 'PO0010', 'Part MS29512-04 is under contract with KLX AEROSPACE SOLUTIONS (Vendor Number: 50306) | Contract Price: $0.05 EA | Lead time: 7 days | Contract Effective Date: 2017-08-07; Contract Expiration Date: 2018-05-31 | Contracts Contact Info: Name: Sascha Wehske | Email: Sascha.Wehske@KLX.com | Contract Ref: LD7023 | 2017-08-08 :SS:

DELTA BUYER COMMENTS: SHELF LIFE 180 MONTHS CANNOT BE OLDER THAN 36 QTRS PAST THE LAST DAY OF QTR OF CURE DATE WHEN RECEIVED FROM VENDOR 1 PER PACK PER ATA SPEC 300

ATTN DELTA BUYER: Cut and Paste the following DELTA INSPECTION COMMENTS into the PO L/I internal narrative. 

ID PACKAGE W/PN/QTY/UNIT CURE DATE', 'SHELF LIFE 180 MONTHS CANNOT BE OLDER THAN 36 QTRS PAST THE LAST DAY OF QTR OF CURE DATE WHEN RECEIVED FROM VENDOR 1 PER PACK PER ATA SPEC 300', 
    'ID PACKAGE W/PN/QTY/UNIT CURE DATE');
Insert into DSS.SAMPLE_TAB6
   (DIV_NO, PSPEC_ID, MODUL_SHORT_NAME, MESSAGE, PO_MESSAGE, 
    INSP_MESSAGE)
 Values
   (5, 1787940, 'PO0010', 'Part MS20392-2C31 is under contract with KLX AEROSPACE SOLUTIONS (Vendor Number: 50306) | Contract Price: $0.31 EA | Lead time: 5 days | Contract Effective Date: 2017-08-07; Contract Expiration Date: 2018-05-31 | Contracts Contact Info: Name: Sascha Wehske | Email: Sascha.Wehske@KLX.com | Contract Ref: LD7023 | 2017-08-08 :SS:

ATTN DELTA BUYER: Cut and Paste the following DELTA INSPECTION COMMENTS into the PO L/I internal narrative. 

ALT AN393-31         V15081', NULL, 
    'ALT AN393-31         V15081');
Insert into DSS.SAMPLE_TAB6
   (DIV_NO, PSPEC_ID, MODUL_SHORT_NAME, MESSAGE, PO_MESSAGE, 
    INSP_MESSAGE)
 Values
   (5, 7173720, 'PO0010', 'DELTA BUYER COMMENTS: MUST BE 5 LB SPOOLS', 'MUST BE 5 LB SPOOLS', 
    NULL);
COMMIT;

Open in new window


4 lines of data. If message has part message and below delta buyer comments, have to append only delta buyer comments with with po_message and insp message if exists. if only one message(either po/insp message) then append. if both messages exist then append after part message with DELTA BUYER COMMENTS : -------------------------

If part message is not there but has only delta buyer comments then append with po/insp message

any clarification please let me know
0
 
anumosesAuthor Commented:
sujith your code works

select message,po_message,insp_message,regexp_replace(message, '(DELTA BUYER COMMENTS.*)', 'DELTA BUYER COMMENTS:  ',1,1,'n')||
            NVL2(po_message, chr(10)||po_message, NULL)||
            NVL2(insp_message, chr(10)||insp_message, null) from sample_tab6

Open in new window


But I need

Part MS29512-04 is under contract with KLX AEROSPACE SOLUTIONS (Vendor Number: 50306) | Contract Price: $0.05 EA | Lead time: 7 days | Contract Effective Date: 2017-08-07; Contract Expiration Date: 2018-05-31 | Contracts Contact Info: Name: Sascha Wehske | Email: Sascha.Wehske@KLX.com | Contract Ref: LD7023 | 2017-08-08 :SS:

DELTA BUYER COMMENTS:  
SHELF LIFE 180 MONTHS CANNOT BE OLDER THAN 36 QTRS PAST THE LAST DAY OF QTR OF CURE DATE WHEN RECEIVED FROM VENDOR 1 PER PACK PER ATA SPEC 300
ID PACKAGE W/PN/QTY/UNIT CURE DATE

instead it should look like

Part MS29512-04 is under contract with KLX AEROSPACE SOLUTIONS (Vendor Number: 50306) | Contract Price: $0.05 EA | Lead time: 7 days | Contract Effective Date: 2017-08-07; Contract Expiration Date: 2018-05-31 | Contracts Contact Info: Name: Sascha Wehske | Email: Sascha.Wehske@KLX.com | Contract Ref: LD7023 | 2017-08-08 :SS:

DELTA BUYER COMMENTS:  SHELF LIFE 180 MONTHS CANNOT BE OLDER THAN 36 QTRS PAST THE LAST DAY OF QTR OF CURE DATE WHEN

RECEIVED FROM VENDOR 1 PER PACK PER ATA SPEC 300

ID PACKAGE W/PN/QTY/UNIT CURE DATE
0
 
anumosesAuthor Commented:
DELTA BUYER COMMENTS:  
MUST BE 5 LB SPOOLS

need like this

DELTA BUYER COMMENTS:  MUST BE 5 LB SPOOLS
0
 
SujithData ArchitectCommented:
The SQL I have given will give you the expected output. If you run it from SQLPlus it will give the results as you are expecting. The line breaks might be something your UI application is introducing.
0
 
anumosesAuthor Commented:
Part MS20392-2C31 is under contract with KLX AEROSPACE SOLUTIONS (Vendor Number: 50306) | Contract Price: $0.31 EA | Lead time: 5 days | Contract Effective Date: 2017-08-07; Contract Expiration Date: 2018-05-31 | Contracts Contact Info: Name: Sascha Wehske | Email: Sascha.Wehske@KLX.com | Contract Ref: LD7023 | 2017-08-08 :SS:

ATTN DELTA BUYER: Cut and Paste the following DELTA INSPECTION COMMENTS into the PO L/I internal narrative.

ALT AN393-31         V15081

There is one more scenario where it has ATTN DELTA BUYER. So that line has to go and the below has to be displayed

Part MS20392-2C31 is under contract with KLX AEROSPACE SOLUTIONS (Vendor Number: 50306) | Contract Price: $0.31 EA | Lead time: 5 days | Contract Effective Date: 2017-08-07; Contract Expiration Date: 2018-05-31 | Contracts Contact Info: Name: Sascha Wehske | Email: Sascha.Wehske@KLX.com | Contract Ref: LD7023 | 2017-08-08 :SS:

DELTA BUYER COMMENTS : ALT AN393-31         V15081
0
 
SujithData ArchitectCommented:
if you dont want line breaks, remove the CHR(10) wherever it is not required.
For example: If you dont require a line break after DELTA BUYER COMMENTS: remove the first chr(10). I guess you need to play around with the SQL a bit to adjust to what you are expecting.

I.e.
   select   regexp_replace(message, '(DELTA BUYER COMMENTS.*)', 'DELTA BUYER COMMENTS:  ',1,1,'n')||
            NVL2(po_message, po_message, NULL)||
            NVL2(insp_message, chr(10)||insp_message, null)   str,  message
   from sample_tab6
   where pspec_id = 7173720
   and div_no = 5
   and modul_short_name = 'PO0010'
0
 
anumosesAuthor Commented:
Part MS20392-2C31 is under contract with KLX AEROSPACE SOLUTIONS (Vendor Number: 50306) | Contract Price: $0.31 EA | Lead time: 5 days | Contract Effective Date: 2017-08-07; Contract Expiration Date: 2018-05-31 | Contracts Contact Info: Name: Sascha Wehske | Email: Sascha.Wehske@KLX.com | Contract Ref: LD7023 | 2017-08-08 :SS:

ATTN DELTA BUYER: Cut and Paste the following DELTA INSPECTION COMMENTS into the PO L/I internal narrative.

ALT AN393-31         V15081

ALT AN393-31         V15081

In this sample data, need to replace

ATTN DELTA BUYER: Cut and Paste the following DELTA INSPECTION COMMENTS into the PO L/I internal narrative.
0
 
SujithData ArchitectCommented:
you can handle multiple Patterns in the regular expression, if there are two patterns separate them by PIPE as in here -


   select   regexp_replace(message, '(DELTA BUYER COMMENTS|ATTN DELTA BUYER).*', 'DELTA BUYER COMMENTS:  ',1,1,'n')||
            NVL2(po_message, po_message, NULL)||
            NVL2(insp_message, NVL2(po_message, chr(10), NULL) ||insp_message, null)   str,  message
   from sample_tab6
   where pspec_id = 7173720
   and div_no = 5
   and modul_short_name = 'PO0010'
0
 
anumosesAuthor Commented:
 select   regexp_replace(message, '(DELTA BUYER COMMENTS|ATTN DELTA BUYER).*', 'DELTA BUYER COMMENTS:  ',1,1,'n')||
            NVL2(po_message, po_message, NULL)||chr(10) ||
            NVL2(insp_message, NVL2(po_message, chr(10), NULL) ||insp_message, null)   str,  message
   from sample_tab6;

Open in new window


Looks good but need help in one like data

Part MS20392-2C31 is under contract with KLX AEROSPACE SOLUTIONS (Vendor Number: 50306) | Contract Price: $0.31 EA | Lead time: 5 days | Contract Effective Date: 2017-08-07; Contract Expiration Date: 2018-05-31 | Contracts Contact Info: Name: Sascha Wehske | Email: Sascha.Wehske@KLX.com | Contract Ref: LD7023 | 2017-08-08 :SS:

DELTA BUYER COMMENTS:  
ALT AN393-31         V15081

Needs to be

Part MS20392-2C31 is under contract with KLX AEROSPACE SOLUTIONS (Vendor Number: 50306) | Contract Price: $0.31 EA | Lead time: 5 days | Contract Effective Date: 2017-08-07; Contract Expiration Date: 2018-05-31 | Contracts Contact Info: Name: Sascha Wehske | Email: Sascha.Wehske@KLX.com | Contract Ref: LD7023 | 2017-08-08 :SS:

DELTA BUYER COMMENTS:  ALT AN393-31         V15081
0
 
SujithData ArchitectCommented:
It works as it needs to be in the query I posted. Looks like you have added another chr(10).  There is no line break in my query after DELTA BUYER COMMENTS:

   select   regexp_replace(message, '(DELTA BUYER COMMENTS|ATTN DELTA BUYER).*', 'DELTA BUYER COMMENTS:  ',1,1,'n')||
            NVL2(po_message, po_message, NULL)||
            NVL2(insp_message, NVL2(po_message, chr(10), NULL) ||insp_message, null)   str,  message
   from sample_tab6
0
 
anumosesAuthor Commented:
Part MS29512-04 is under contract with KLX AEROSPACE SOLUTIONS (Vendor Number: 50306) | Contract Price: $0.05 EA | Lead time: 7 days | Contract Effective Date: 2017-08-07; Contract Expiration Date: 2018-05-31 | Contracts Contact Info: Name: Sascha Wehske | Email: Sascha.Wehske@KLX.com | Contract Ref: LD7023 | 2017-08-08 :SS:

DELTA BUYER COMMENTS:  SHELF LIFE 180 MONTHS CANNOT BE OLDER THAN 36 QTRS PAST THE LAST DAY OF QTR OF CURE DATE WHEN RECEIVED FROM VENDOR 1 PER PACK PER ATA SPEC 300
ID PACKAGE W/PN/QTY/UNIT CURE DATE

Only this needs a line break

Part MS29512-04 is under contract with KLX AEROSPACE SOLUTIONS (Vendor Number: 50306) | Contract Price: $0.05 EA | Lead time: 7 days | Contract Effective Date: 2017-08-07; Contract Expiration Date: 2018-05-31 | Contracts Contact Info: Name: Sascha Wehske | Email: Sascha.Wehske@KLX.com | Contract Ref: LD7023 | 2017-08-08 :SS:

DELTA BUYER COMMENTS:  SHELF LIFE 180 MONTHS CANNOT BE OLDER THAN 36 QTRS PAST THE LAST DAY OF QTR OF CURE DATE WHEN RECEIVED FROM VENDOR 1 PER PACK PER ATA SPEC 300

ID PACKAGE W/PN/QTY/UNIT CURE DATE
0
 
anumosesAuthor Commented:
I think when both message (po message and insp message ) are there need a chr(10)
0
 
SujithData ArchitectCommented:
You are not clearly explaining what you need. You can try placing the conditional chr(10) to suit what you need.

   select   regexp_replace(message, '(DELTA BUYER COMMENTS|ATTN DELTA BUYER).*', 'DELTA BUYER COMMENTS:  ',1,1,'n')||
            NVL2(po_message, NVL2(insp_message, chr(10), NULL)||po_message, NULL)||
            NVL2(insp_message, NVL2(po_message, chr(10), NULL) ||insp_message, null)   str, po_message, insp_message 
   from sample_tab6 a

Open in new window

0
 
anumosesAuthor Commented:
Sorry to confuse you. I am not changing your code. Wanted a empty line after the delta buyer comments line in case there is a po_message and or insp message.

Previous code is good but

DELTA BUYER COMMENTS:  SHELF LIFE 180 MONTHS CANNOT BE OLDER THAN 36 QTRS PAST THE LAST DAY OF QTR OF CURE DATE WHEN RECEIVED FROM VENDOR 1 PER PACK PER ATA SPEC 300 (Po message)

ID PACKAGE W/PN/QTY/UNIT CURE DATE (insp message)

Needed a line gap.
0
 
SujithData ArchitectCommented:
Have you tried this?

   select   regexp_replace(message, '(DELTA BUYER COMMENTS|ATTN DELTA BUYER).*', 'DELTA BUYER COMMENTS:  ',1,1,'n')||
            NVL2(po_message, NVL2(insp_message, chr(10), NULL)||po_message, NULL)||
            NVL2(insp_message, NVL2(po_message, chr(10), NULL) ||insp_message, null)   str, po_message, insp_message
   from sample_tab6 a
0
 
anumosesAuthor Commented:
Yes tried

Part MS29512-04 is under contract with KLX AEROSPACE SOLUTIONS (Vendor Number: 50306) | Contract Price: $0.05 EA | Lead time: 7 days | Contract Effective Date: 2017-08-07; Contract Expiration Date: 2018-05-31 | Contracts Contact Info: Name: Sascha Wehske | Email: Sascha.Wehske@KLX.com | Contract Ref: LD7023 | 2017-08-08 :SS:

DELTA BUYER COMMENTS:  
SHELF LIFE 180 MONTHS CANNOT BE OLDER THAN 36 QTRS PAST THE LAST DAY OF QTR OF CURE DATE WHEN RECEIVED FROM VENDOR 1 PER PACK PER ATA SPEC 300
ID PACKAGE W/PN/QTY/UNIT CURE DATE

Required

DELTA BUYER COMMENTS:  SHELF LIFE 180 MONTHS CANNOT BE OLDER THAN 36 QTRS PAST THE LAST DAY OF QTR OF CURE DATE WHEN RECEIVED FROM VENDOR 1 PER PACK PER ATA SPEC 300

ID PACKAGE W/PN/QTY/UNIT CURE DATE
0
 
anumosesAuthor Commented:
After part message
0
 
anumosesAuthor Commented:
Thanks for the timely help
0
 
SujithData ArchitectCommented:
Thank You for promptly closing the thread! Glad to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.