Oracle query

CREATE TABLE DSS.SAMPLE_TAB1
(
  DIVNO         NUMBER(2),
  FSC           VARCHAR2(5 BYTE),
  PART_NUMBER   VARCHAR2(40 BYTE),
  SCREEN        VARCHAR2(10 BYTE),
  PO_MESSAGE    VARCHAR2(4000 BYTE),
  INSP_MESSAGE  VARCHAR2(4000 BYTE)
)

Insert into DSS.SAMPLE_TAB1
   (DIVNO, FSC, PART_NUMBER, SCREEN, PO_MESSAGE, 
    INSP_MESSAGE)
 Values
   (5, '96906', 'MS20427M5-4', 'PO0010', 'THIS IS SOMEWHAT NEW PART THAT HAS TO BE PACKAGED.', 
    NULL);
COMMIT;

Open in new window


CREATE TABLE DSS.SAMPLE_TAB2
(
  PART  VARCHAR2(20 BYTE)                       NOT NULL,
  ID    NUMBER(38)                              NOT NULL,
  FSC   VARCHAR2(5 BYTE)
)

Insert into DSS.SAMPLE_TAB2
   (PART, ID, FSC)
 Values
   ('MS20427M5-4', 2683485, '96906');
COMMIT;

Open in new window


CREATE TABLE DSS.SAMPLE_TAB3
(
  ID                NUMBER(38)                  NOT NULL,
  DIV_NO            NUMBER(2)                   NOT NULL,
  MODUL_SHORT_NAME  VARCHAR2(10 BYTE)           NOT NULL,
  MESSAGE           VARCHAR2(4000 BYTE)         NOT NULL,
  ACTIVE            VARCHAR2(1 BYTE)            NOT NULL,
  PSPEC_ID          NUMBER(38),
  CUST_ID           NUMBER(38),
  VEND_ID           NUMBER(38)
)

Insert into DSS.SAMPLE_TAB3
   (ID, DIV_NO, MODUL_SHORT_NAME, MESSAGE, ACTIVE, 
    PSPEC_ID, CUST_ID, VEND_ID)
 Values
   (9948505115, 5, 'PO0010', 'Part MS20427M5-4 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: PACKAGE 1LB PER PACKAGE IDENTIFY PACKAGE WITH PART NUMBER/QUANTITY/UNIT MUST BE PACKAGED 1 LB PER PKG. MUST BE PACKAGED 1LB PER PKG. MUST BE UNITIZED 1LB PER PKG.', 'A', 
    2683485, NULL, NULL);
COMMIT;

Open in new window


 select DIVNO                                                                 
         , FSC                                                                                                             
         , PART_NUMBER                                                                                                     
         , SCREEN                                                                                                     
         , UPPER(PO_MESSAGE) AS PO_MESSAGE                                                                                                   
         , UPPER(INSP_MESSAGE) AS INSP_MESSAGE            
      from sample_tab1
    WHERE ((PO_MESSAGE IS NOT NULL AND INSP_MESSAGE IS NOT NULL)
         OR (PO_MESSAGE IS NOT NULL OR INSP_MESSAGE IS NOT NULL))
         and part_number in ('MS20427M5-4')
      and  divno=5 
      and fsc in ('96906');
      
   
      select  *     
  from sample_tab2 
   where  part in ('MS20427M5-4')
   and fsc in ('96906');
   

   select * from sample_tab3
   where pspec_id = 2683485
   and div_no = 5
   and modul_short_name = 'PO0010'

   
 update sample_tab3
   set message = 'DELTA BUYER COMMENTS:'
    || decode(nvl('add new text','XYZ'),'XYZ',chr(10)||chr(10),chr(10)||chr(10)||'some message'||chr(10)||chr(10))
    || 'may be'
 where pspec_id = 2683485
   and modul_short_name = 'PO0010'        
   and div_no = 5;

Open in new window


Need to delete only

DELTA BUYER COMMENTS: PACKAGE 1LB PER PACKAGE IDENTIFY PACKAGE WITH PART NUMBER/QUANTITY/UNIT MUST BE PACKAGED 1 LB PER PKG. MUST BE PACKAGED 1LB PER PKG. MUST BE UNITIZED 1LB PER PKG.

and add

DELTA BUYER COMMENTS:  THIS IS SOMEWHAT NEW PART THAT HAS TO BE PACKAGED.

leaving

'Part MS20427M5-4 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:

AS IS..

I have have to take instr but help is appreciated.
LVL 6
anumosesAsked:
Who is Participating?
 
SujithData ArchitectCommented:
Use this update

 update sample_tab3
   set message = regexp_replace(message, '(DELTA BUYER COMMENTS.*)', 'DELTA BUYER COMMENTS:  THIS IS SOMEWHAT NEW PART THAT HAS TO BE PACKAGED.')
 where pspec_id = 2683485
   and modul_short_name = 'PO0010'        
   and div_no = 5;
0
 
anumosesAuthor Commented:
Thanks . Will close this and I am having another question posted with a slight difference as to what the user is asking. Created a new post.
0
 
anumosesAuthor Commented:
thanks
0
 
SujithData ArchitectCommented:
Glad to help! cheers.
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.