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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.